Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Have one requirement is that, Have 2 string columns
Need to derive date (DD/MM/YYYY hh:mm) format from those 2 columns .
Individually I tried to extract using Subfield and concatenate but the issue is with month I need to convert to number like for e.g Jan to 01 and similarly for all months
PFA sample app for reference.
Sample:
LOAD *,Timestamp(timestamp#(Left([Start Date],23),'MMM/DD/YYYY hh:mm:ss TT')) as SD,Timestamp(timestamp#(Left([End Date],23),'MMM/DD/YYYY hh:mm:ss TT')) as ED INLINE [
Start Date, End Date
Sep/26/2014 01:07:39 AM IST, Sep/27/2014 01:07:39 AM IST
Dec/10/2014 04:35:45 AM IST, Dec/11/2014 04:35:45 AM IST
Jan/13/2015 02:35:37 AM IST, Jan/14/2015 02:35:37 AM IST
];
Regards,
Kaushik Solanki
Try this.
Sample:
LOAD *,Date(Date#(Left([Start Date],11),'MMM/DD/YYYY')) as SD,Date(Date#(Left([End Date],11),'MMM/DD/YYYY')) as ED INLINE [
Start Date, End Date
Sep/26/2014 01:07:39 AM IST, Sep/27/2014 01:07:39 AM IST
Dec/10/2014 04:35:45 AM IST, Dec/11/2014 04:35:45 AM IST
Jan/13/2015 02:35:37 AM IST, Jan/14/2015 02:35:37 AM IST
];
Regards,
Kaushik Solanki
Try this with subfield and Date
Sample:
LOAD *,
Date(Date#([Start Date],'MMM/DD/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm') as [Start Date New],
Date(Date#([End Date],'MMM/DD/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm') as [End Date New];
LOAD
Mid([Start Date],1,Len([Start Date])-3) as [Start Date],
Mid([End Date],1,Len([End Date])-3) as [End Date];
LOAD * INLINE [
Start Date, End Date
Sep/26/2014 01:07:39 AM IST, Sep/27/2014 01:07:39 AM IST
Dec/10/2014 04:35:45 AM IST, Dec/11/2014 04:35:45 AM IST
Jan/13/2015 02:35:37 AM IST, Jan/14/2015 02:35:37 AM IST
];
See attached also
Hi Kaushik,
thank you.
I need to have time stamp as well, because I need to calculate ED-CD and so.
Could you please let me know how to include time stamp as well
Hi Anand,
Thank you.
My requirement is to convert month like Dec to 12.
Can please let me know how to do the same
Input: Dec/10/2014 04:35:45 AM IST
Expected output: 10/12/2014 04:35
Sample:
LOAD *,Timestamp(timestamp#(Left([Start Date],23),'MMM/DD/YYYY hh:mm:ss TT')) as SD,Timestamp(timestamp#(Left([End Date],23),'MMM/DD/YYYY hh:mm:ss TT')) as ED INLINE [
Start Date, End Date
Sep/26/2014 01:07:39 AM IST, Sep/27/2014 01:07:39 AM IST
Dec/10/2014 04:35:45 AM IST, Dec/11/2014 04:35:45 AM IST
Jan/13/2015 02:35:37 AM IST, Jan/14/2015 02:35:37 AM IST
];
Regards,
Kaushik Solanki
On my above solution it is converted in your expected format kind check
Sample:
LOAD *,
Date(Date#([Start Date],'MMM/DD/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm') as [Start Date New],
Date(Date#([End Date],'MMM/DD/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm') as [End Date New];
LOAD
Mid([Start Date],1,Len([Start Date])-3) as [Start Date],
Mid([End Date],1,Len([End Date])-3) as [End Date];
LOAD * INLINE [
Start Date, End Date
Sep/26/2014 01:07:39 AM IST, Sep/27/2014 01:07:39 AM IST
Dec/10/2014 04:35:45 AM IST, Dec/11/2014 04:35:45 AM IST
Jan/13/2015 02:35:37 AM IST, Jan/14/2015 02:35:37 AM IST
];
OutPut
See attached here
try this
timestamp(Timestamp#(purgechar([Start Date],'IST'),'MMM/DD/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm') as [Start Date],
Kushal purgechar will remove the S from Sep
correct , we can try below then
=timestamp(Timestamp#(mid([Start Date],1,Index([Start Date],'IST')-2),'MMM/DD/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm') as [Start Date]