Extract Date from String

[vardhancse]

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.

[kaushiknsolanki]

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

[its_anandrjs]

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

[vardhancse]

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

[vardhancse]

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

[kaushiknsolanki · Accepted solution]

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

[its_anandrjs]

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

[Kushal_Chawda]

try this

timestamp(Timestamp#(purgechar([Start Date],'IST'),'MMM/DD/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm') as [Start Date],

[its_anandrjs]

Kushal purgechar will remove the S from Sep

[Kushal_Chawda]

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]