Flooring UK and US Date/Time stamps

[shane_spencer]

I've got 2 sets of data I am combing, the first with US style timestamp (i.e. 08/09/2015  00:00:22 is 9th August) and the second with UK style timestamp (i.e. 01/08/2015  01:43:07 is 1st August). I want to combine the Date from these two timestamps in to a single Date field so I use the following:

     Date(Floor(RetrievedDt),'MM/DD/YYYY') as Date,

for the US timestamp and for the UK timestamp:

     Date(Floor([Start Time]),'DD/MM/YYYY') as Date,

Unfortunately when I store them they are not stored in the same format, so they appear the same but when I try to combine them I get 2 entries for each date. My variables are set as follows. What's the best way to sort this (so both dates are stored in UK format DD/MM/YYY) ?

SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

[sunny_talwar]

Try this may be:

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

US Date to UK Floored Date

Date(Floor(Date#(RetrievedDt, 'MM/DD/YYYY hh:mm:ss'))) as Date

UK Date to UK Floored Date

Date(Floor(Date#([Start Time], 'DD/MM/YYYY hh:mm:ss'))) as Date

[swuehl]

Try

   Date(Floor(Timestamp#(RetrievedDt,'MM/DD/YYYY hh:mm:ss')),'DD/MM/YYYY') as Date,

for the US timestamp and for the UK timestamp:

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

So first interprete the input data Format correctly, then floor() and reformat. And check all format codes, e.g. if you need AM/PM parsing 'TT')

[shane_spencer]

The following still seems to interpret the date as being UK format, when it's actually US so it's storing it incorrectly

     Date#(RetrievedDt,'MM/DD/YYYY') as Date,

[shane_spencer]

I think the trouble is the excel spreadsheet thinks this date is UK format, but it's actually US.

[sunny_talwar]

Try this instead:

Date(Floor(Date#(RetrievedDt, 'MM/DD/YYYY hh:mm:ss TT')), 'DD/MM/YYYY') as Date

[shane_spencer · Accepted solution]

Thanks for your help guys, I got it figured:

     Date#(Date(RetrievedDt,'MM/DD/YYYY')) as Date,

As you know Excel stored a date as a Number, and for some reason it was incorrectly interpreting these US dates as UK so saving them incorrectly. I had to trick QlikView into swapping the MM and DD round then storing as a Date. Not sure what's going to happen when I get some data where the date cannot be either US or UK, i.e. from the 13th of the Month.