Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Geniuses,
I am facing problem while converting timestamp to date
I have the timestap coming as text from source like below
4/6/2016 9:52 AM |
4/6/2016 10:28 AM |
4/6/2016 10:32 AM |
4/6/2016 10:40 AM |
4/6/2016 11:35 AM |
4/6/2016 11:38 AM |
4/6/2016 11:41 AM |
4/6/2016 11:42 AM |
I want to first convert it into date and then change that date into number
As i have to calculate the open days for the ticket
Thanks in advance
Shashank
Hi,
May be like this,
Data:
LOAD *,
Date(Timestamp#(Field,'DD/MM/YYYY h:mm TT'),'DD/MM/YYYY') as Date,
Num(Date(Timestamp#(Field,'DD/MM/YYYY h:mm TT'),'DD/MM/YYYY')) as NumDate
INLINE [
Field
4/6/2016 9:52 AM
4/6/2016 10:28 AM
4/6/2016 10:32 AM
4/6/2016 10:40 AM
4/6/2016 11:35 AM
4/6/2016 11:38 AM
4/6/2016 11:41 AM
4/6/2016 11:42 AM
];
Output:
HTH,
PFA,
Hirish
=Date(DATE#(Datefield,'D/M/YYYY hh:ss T' ) 'DD/MM/YYYY') AS new date.
What is ur required date format?
=Num(Date field)
Hi,
May be like this,
Data:
LOAD *,
Date(Timestamp#(Field,'DD/MM/YYYY h:mm TT'),'DD/MM/YYYY') as Date,
Num(Date(Timestamp#(Field,'DD/MM/YYYY h:mm TT'),'DD/MM/YYYY')) as NumDate
INLINE [
Field
4/6/2016 9:52 AM
4/6/2016 10:28 AM
4/6/2016 10:32 AM
4/6/2016 10:40 AM
4/6/2016 11:35 AM
4/6/2016 11:38 AM
4/6/2016 11:41 AM
4/6/2016 11:42 AM
];
Output:
HTH,
PFA,
Hirish
Not sure your what you are trying to do. Try,
Num(Floor(Date#(Datefield,'M/D/YYYY h:mm TT')))
Small change to my exp.
Data:
LOAD *,
num(Date(DATE#(Datefield,'D/M/YYYY hh:ss TT') ,'DD/MM/YYYY')) AS new1
INLINE [
Datefield
4/6/2016 9:52 AM
4/6/2016 10:28 AM
4/6/2016 10:32 AM
4/6/2016 10:40 AM
4/6/2016 11:35 AM
4/6/2016 11:38 AM
4/6/2016 11:41 AM
4/6/2016 11:42 AM];
Hi,
Try like this
Floor(DATE#(Datefield,'M/D/YYYY hh:ss TT' ) )
Regards,
Jagan.