Date/Time format issue

[arpitkharkia]

Hi all,

I have a timestamp field which consists date of the following two formats:

• MM-DD-YYYY HH:MM
• MM/DD/YYYY HH:MM:SS TT

I want to extract date and time separately in the following formats:

• MM-DD-YYYY
• HH:MM:SS

Thanks in advance!

[arpitkharkia]

if you look at the circled values , they are the 2nd day of different months. The file I have posted only contains Feb month data.

[arpitkharkia]

Thanks its looking good. But its not showing dates from 1st to 12th Feb?

[sunny_talwar]

Seems like a problem within your Excel file itself.... The Call_Date shows it as April for the first entry... check it out

[arpitkharkia]

Oh yeah. May be the file im getting is in that format only. Is it any way that I can extract dates from there considering that data is only for the month of Feb? Help appreciated!

[tresesco]

That is because your those dates are being read as numbers and not like you see in the excel. If you try with the below script, you will get all of them but with different dates that don't expect. Here you won't be able to handle that in qv rather you have to change in the source, i.e. excel per se.

Date((Alt(

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm'),

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY  hh:mm:ss TT'), Call_Date               // Call_Date when numeric

)), 'MMM-DD-YYYY') as Date

[tresesco]

May be like:

LOAD

Date(Floor(Alt(

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm'),

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm:ss TT'),

Call_Date

)), 'MMM-DD-YYYY') as Date, Call_Date

FROM

<>

Where

Month(Alt(

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm'),

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm:ss TT'),

Call_Date

)) ='Feb';

[sunny_talwar · Accepted solution]

Here you go... try this

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss TT';

Table:

LOAD Call_Date,

  Date(Alt(Date#(Date(Num#(Text(Call_Date))), 'D/M/YYYY'), Date(Floor(Call_Date))), 'MM-DD-YYYY') as Date,

  Time(Frac(Call_Date), 'hh:mm:ss') as Time

FROM

[CallDate.xlsx]

(ooxml, embedded labels, table is Sheet1);