Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arpitkharkia
Creator III
Creator III

Date/Time format issue

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!

16 Replies
arpitkharkia
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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

Capture.PNG

arpitkharkia
Creator III
Creator III
Author

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
MVP
MVP

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


Capture.PNG

tresesco
MVP
MVP

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

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);


Capture.PNG