6 Replies Latest reply: Mar 3, 2014 7:03 AM by Stian Karlsen RSS

    Split date and time from a time field.

      Hi, I have found the need to split date and time from a timestamp field.

      This is how the field looks like in the source:


      I have managed to split out the date by the use of date() - function

      date(floor(create_time),'DD.MM.YYYY') as Dato1


      The tricky part looks to be to just get out the whole hours from the create_time field.

      What I have tried so far without any success.


      frac(round(create_time,(1/24))) as [Incoming Time],

      time(frac(create_time),'hh')   as [Incomming Time], Time#(Right(create_time,8),'hh') as [Incoming Time],

      Time(Frac(TimeStamp#(create_time,'DD.MM.YYYY hh:mm:ss')),'hh') as [Incoming Time],

      date(floor(create_time),'hh') as [Incoming Time],


      Does anyone have any idea on how to achieve a "timestamp" with just the whole hour?

      In my case, i just want 24 unique values from 00 - 23. From all the 21 million rows in the create_time field.


      By using frac(round(create_time,(1/24))) as [Incoming Time],

      I got this result, but the end result should be without Date and mm:ss just the hh left.


      - Stian K.