Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi
You were almost there, I think with
frac(round(create_time,(1/24)))
Now just rearrange and format that - I think like this:
Time(Round(Frac(create_time), 1/24), 'hh')
HTH
Jonathan
may be something like below .... not 100% sure
=Time(create_time,'hh:00:00')
You can use Dual function to split the date and time
=Dual( Date( Floor( Date ), 'YYYY-MM-DD') & ' _time ' Time( Frac( Timestamp#( Created, 'YYYY-MM-DD hh:mm:ss')), 'hh:mm:ss') as Incoming Time
Hope it helps
Hi
You were almost there, I think with
frac(round(create_time,(1/24)))
Now just rearrange and format that - I think like this:
Time(Round(Frac(create_time), 1/24), 'hh')
HTH
Jonathan
Or even this:
Round(Frac(create_time) * 24), 1)
Yes, this also worked. Just added the missing (
Round((Frac(create_time) * 24), 1) as [Incoming Time],
Thanks worked like a charm