Problem with Numeric and string formats in Date field

[pavan045]

Hi all,

I have 2 date feilds in a table in excel file, but some of the dates are in string format and some are in number format in my excel file.

See below table,

EmpId	StartDate	EndDate
1	30/09/2014	20/01/2015
1	21/01/2015	2/3/2015
1	3/3/2014	31/01/2016
2	10/3/2015	21/06/2015
2	22/06/2015	20/09/2015
6	10/11/2015	31/01/2016
7	2/10/2015	31/01/2016
8	17/11/2014	22/02/2015
8	23/02/2015	4/3/2015
8	5/3/2015	31/01/2016
9	18/11/2014	2/3/2015
9	3/3/2015	31/01/2016

Now I want to make all dates into a single format.i.e Number format in qlikview. I have tried to used DATE()  and DATE#() functions, but I think those are not giving desired results.

any ideas please?..

Thank you.

[sunny_talwar]

How would you identify the format of these dates:

[#3]

can you post your sample excel file.

or

use makedate function it may help.

[jonathandienst]

The Alt function is a great way of trying out multiple parse strings and returns the first one that successfully parses. Use something like:

Date(Alt(StartDate, Date#(StartDate, 'dd/MM/yyyy'), Date#(StartDate,'d/M/yyyy))) as StartDate,

This will parse a numeric value, but if it is a string, will try dd/MM/yyyy and then d/M/yyyy to get the date,.

[kkkumar82]

As jonathan said, if you know the Date formats of the files then using alt() is a convenient one

[jlongoria]

First read this very good post on Date and Date#: The Date Function

Either load script will work depending on your default date/timestamp format settings. If you have a mix of M/D/YYYY and D/M/YYYY date formats, you'll need some kind of indicator field to determine whether to use one format or the other or you'll need to make a logic decision as you load data to make one format the primary format.

If your default format is M/D/YYYY and your data is in D/M/YYYY format, you need to use Date#.

SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
LOAD
     *,
     Date#(StartDate, 'DD/MM/YYYY') as [Date#StartDate],
     Date#(EndDate, 'DD/MM/YYYY') as [End#StartDate],
     Date(Date#(StartDate, 'DD/MM/YYYY'), 'MMM-DD-YYYY') as [MMM-DD-YYYYStartDate],
     Date(Date#(EndDate, 'DD/MM/YYYY'), 'MMM-DD-YYYY') as [MMM-DD-YYYYEndDate]
;
LOAD * INLINE [
     EmpId, StartDate, EndDate
     1, 30/09/2014, 20/01/2015
     1, 21/01/2015, 2/3/2015
     1, 3/3/2014, 31/01/2016
     2, 10/3/2015, 21/06/2015
     2, 22/06/2015, 20/09/2015
     6, 10/11/2015, 31/01/2016
     7, 2/10/2015, 31/01/2016
     8, 17/11/2014, 22/02/2015
     8, 23/02/2015, 4/3/2015
     8, 5/3/2015, 31/01/2016
     9, 18/11/2014, 2/3/2015
     9, 3/3/2015, 31/01/2016
     ]

;

If your default format is D/M/YYYY and your data is in D/M/YYYY format there is no need to use Date#

SET DateFormat='D/M/YYYY';
SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';
LOAD
     *,
     Date(StartDate, 'MMM-DD-YYYY') as [MMM-DD-YYYYStartDate],
     Date(EndDate, 'MMM-DD-YYYY') as [MMM-DD-YYYYEndDate]
;
LOAD * INLINE [
     EmpId, StartDate, EndDate
     1, 30/09/2014, 20/01/2015
     1, 21/01/2015, 2/3/2015
     1, 3/3/2014, 31/01/2016
     2, 10/3/2015, 21/06/2015
     2, 22/06/2015, 20/09/2015
     6, 10/11/2015, 31/01/2016
     7, 2/10/2015, 31/01/2016
     8, 17/11/2014, 22/02/2015
     8, 23/02/2015, 4/3/2015
     8, 5/3/2015, 31/01/2016
     9, 18/11/2014, 2/3/2015
     9, 3/3/2015, 31/01/2016
     ]

;

[pavan045]

Hi Sunny,

Please refer attached excel file for date format.

when I use Date#() function result is like below image.

here some dates are in number format so Date#() function interprest the dates which are in string format.

if I use Date() function  result is like this(there no third date associated with EmpId 1)

I want all dates to be shown in same format.