Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
How to convert the below string into Date&Time Format ? If data coming in different formats then how it should be handled ?
"Thu 3/9/2017 6:10 PM"
Like this
Table:
LOAD Date as Old_Date,
TimeStamp(Alt(Num(Date#(Date, 'M/D/YYYY')), Num(TimeStamp#(Trim(Mid(Date, FindOneOf(Date, '1234567890'))), 'M/D/YYYY h:mm TT')))) as New_Date;
LOAD * Inline [
Date
"Thu 3/9/2017 6:10 PM"
2/22/2017
];
Hi Manoj,
use
date(date#(),required date format)
REgards
SAthish
May be something like this:
=TimeStamp(TimeStamp#(Trim(Mid('Thu 3/9/2017 6:10 PM', FindOneOf('Thu 3/9/2017 6:10 PM', '1234567890'))), 'M/D/YYYY h:mm TT'))
Hi,
try date(date#(dateField,'www D/M/YYYY hh:mm TT'))
Regards,
If there are more than 1 format then use alt()
see alt() in help menu for details.
Regards
Not Working Prashant
I did try this dreamer4, but for some reason, it did not work for me. Have you tested this out and its working for you? Can you share a sample where it is working?
Thanks,
Sunny
Did you try this?
=TimeStamp(TimeStamp#(Trim(Mid('Thu 3/9/2017 6:10 PM', FindOneOf('Thu 3/9/2017 6:10 PM', '1234567890'))), 'M/D/YYYY h:mm TT'))
or this for your date field
=TimeStamp(TimeStamp#(Trim(Mid(DateFieldName, FindOneOf(DateFieldName, '1234567890'))), 'M/D/YYYY h:mm TT'))
Thanks Sunny. It worked
How to handle the below scenarios, both are coming in same column with different formats.
"Thu 3/9/2017 6:10 PM"
2/22/2017
Like this
Table:
LOAD Date as Old_Date,
TimeStamp(Alt(Num(Date#(Date, 'M/D/YYYY')), Num(TimeStamp#(Trim(Mid(Date, FindOneOf(Date, '1234567890'))), 'M/D/YYYY h:mm TT')))) as New_Date;
LOAD * Inline [
Date
"Thu 3/9/2017 6:10 PM"
2/22/2017
];