Timestamp, Datestamp, Time, Date etc etc etc

[dmac1971]

Need a definitive guide on extracting date and time from a timestamp, having diffs with it!

Data comes in from a csv file, in the file the data is displayed as "01/07/2015  06:53:00", once imported into QV without any changes the format changes to "01-07-2015  06:53:00".  So it looks like QV has recognised the data as a date etc.

To extract a workable date I'm using :

Date(Timestamp#([Start Date and Time],'DD-MM-YYYY')) As StartDate,

And this seems to work ok as I can then generate my calendar etc.  How do I extract the time, and just the time?

[sunny_talwar]

Try this:

Date

Date(Floor(Timestamp#([Start Date and Time],'DD-MM-YYYY hh:mm:ss'))) As StartDate,

Time

Time(Frac(Timestamp#([Start Date and Time],'DD-MM-YYYY hh:mm:ss'))) As StartTime,

[dmac1971]

Gives me nothing, thanks though.

[sunny_talwar]

Try this:

Date

Date(Floor(Timestamp#([Start Date and Time],'DD/MM/YYYY hh:mm:ss'))) As StartDate,

Time

Time(Frac(Timestamp#([Start Date and Time],'DD/MM/YYYY hh:mm:ss'))) As StartTime,

or

Date

Date(Floor([Start Date and Time])) As StartDate,

Time

Time(Frac([Start Date and Time])) As StartTime,

[settu_periasamy]

Check your Data...

"01/07/2015  06:53:00"  (did you copy  this or just manual type)

- 2 space between Date and Time (if it is single space, Sunny's suggestion will work)

=Timestamp(Timestamp#('01/07/2015 06:53:00','DD/MM/YYYY hh:mm:ss'),'hh:mm:ss')

[Colin-Albert]

These blogs detail how to process Date & Time data correctly

https://community.qlik.com/blogs/qlikviewdesignblog/2012/06/07/get-the-dates-right

https://community.qlik.com/docs/DOC-3102

[dmac1971]

Thanks for replies all but nothing is working.  Settu well spotted there are indeed two spaces between the date element and the time element, this is in the raw data.

I suppose I could use text commands such as Left and Mid to extract something useable but I'd rather crack this so I can refer to the methodology in future.

Will read up a bit more to see if I can get a solution.

[dmac1971]

Attachment should show time formats.  Still struggling, there are indeed two spaces between date and time in the timestamp.

[sunny_talwar]

Try it with two spaces where we specify format:

Date

Date(Floor(Timestamp#([Start Date and Time],'DD/MM/YYYY hh:mm:ss'))) As StartDate,

Time

Time(Frac(Timestamp#([Start Date and Time],'DD/MM/YYYY hh:mm:ss'))) As StartTime,

[Peter_Cammaert · Accepted solution]

This works on your csv file:

LOAD [Start Date and Time],

    Time(Timestamp#([Start Date and Time],'DD/MM/YYYY hh:mm')) AS [Start Time],

    Date(Timestamp#([Start Date and Time],'DD/MM/YYYY hh:mm')) AS [Start Date],

    [End Date and Time],

    Time(Timestamp#([End Date and Time],'DD/MM/YYYY hh:mm')) AS [End Time],

    Date(Timestamp#([End Date and Time],'DD/MM/YYYY hh:mm')) AS [End Date]

FROM [callLog_18-11-2015_18-11-2015.csv](txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Produces this in a Table Box:

Best,

Peter