6 Replies Latest reply: Nov 13, 2012 1:44 PM by 867823 RSS

    derive an hour of the day

    867823
      Hi,

      I am would like to derive 1:00 AM of sysdate in OBIEE report and use this time in my calculation. Can someone please help me as how to do it?

      Thank You,
      Arch
        • 1. Re: derive an hour of the day
          Voltaire
          Hi,

          HOUR(CURRENT_TIMESTAMP)

          will give you the hour for the sysdate.

          Regards,
          Jay
          • 2. Re: derive an hour of the day
            867823
            Jay,

            I am trying to get the first hour of sysdate for eg: 11/13/2012 01:00:00 AM and set this value to my variable.

            Thank You,
            Arch
            • 3. Re: derive an hour of the day
              Srini VEERAVALLI
              This below exp gives you the required value

              replace(cast(month(current_date) as char)||'/'||cast(day(Time.Date) as char)||'/'||cast(year(current_date) as char),' ','')||' 01:00:00 AM'

              As per your question I think this is answered ;) pls mark if it does

              Edited by: veeravalli on Nov 13, 2012 1:17 PM

              The below Jay's good to go

              Edited by: veeravalli on Nov 13, 2012 1:19 PM
              • 4. Re: derive an hour of the day
                867823
                Hi,

                Thank You for your reply. I am able to produce the date, but I am having problems with date formatting issue. I need to use this value in the timestampdiff function. Basically, I want to set the, 2nd date in timestampdiff to 'sysdate + 1:00am' (1:00 AM of the current day).

                How do I do this?
                • 5. Re: derive an hour of the day
                  Voltaire
                  Hi,

                  TIMESTAMPADD(SQL_TSI_HOUR, 1, cast(cast(CURRENT_DATE as char) as timestamp))

                  Hope this will help you to get the sysdate+1:00 AM

                  And to add it to the timestampdiff:
                  TIMESTAMPDIFF(SQL_TSI_HOUR, CURRENT_TIMESTAMP, (TIMESTAMPADD(SQL_TSI_HOUR, 1, cast(cast(CURRENT_DATE as char) as timestamp))))

                  Regards,
                  Jay

                  Edited by: Voltaire on Nov 13, 2012 11:19 AM
                  • 6. Re: derive an hour of the day
                    867823
                    Thanks Jay. I was doing that but the result I had was not correct to the decimal place (I compared this result to the result of raw data). So, I just cast the current_date as char first and then appended 01:00 am and then converted the entire thing to timestamp. This works to the decimal point.

                    Thanks again.
                    Arch