

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Flooring UK and US Date/Time stamps
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]';
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think the trouble is the excel spreadsheet thinks this date is UK format, but it's actually US.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this instead:
Date(Floor(Date#(RetrievedDt, 'MM/DD/YYYY hh:mm:ss TT')), 'DD/MM/YYYY') as Date


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
