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: 
kateevans2
Contributor
Contributor

Date Conversion Not Working

The original field being loaded is a text field in excel and shows as ,for example "01- JUL-13" i need this to be displayed as 01/07/13 and mappable to another date of the same format, however all the date conversion i have tried do not appear to amend the date format or will bring through a null value instead.

 

I have attempted to use the following 

M_NUMBER,

ND_DATE,
Date#(num#(ND_DATE),'DD/MM/YYYY') as ND_DATE2,
num(ND_DATE) as ND_DATE3,
date(num#(ND_DATE),'DD/MM/YYYY') as ND_DATE4,
Date(num#(ND_DATE), 'DD/MM/YYYY') as ND_DATE5
Date(Floor(ND_DATE), 'DD/MM/YYYY'')as ND_DATE6,
Date(ND_DATE, 'MM/DD/YYYY') as ND_DATE7,

Date(Date#(ND_DATE, 'DD-MMM-YYY), 'DD/MM/YYYY')as ND_DATE8

 

original Data format in attached file any assistance gladly received 

4 Replies
sunny_talwar

Try this

Table:
LOAD Date(Date#(ND_DATE, 'DD-MMM-YY')) as ND_DATE2,
	 ND_DATE,
     CLAIM_NUMBER
FROM
[..\..\Downloads\Dates.xlsx]
(ooxml, embedded labels, table is Sheet1);
ishanbhatt
Creator II
Creator II

Hello,

 

Use below formula.

Date(Floor(num(Date field)))  as Date.

Set Date format in the start.

kateevans2
Contributor
Contributor
Author

Thank you the answer was a cross of your code and an earlier attempt i had

 

 

Date(Date#(ND_DATE,'DD-MMM-YY'), 'DD/MM/YYYY') 

 

many thanks for your help 

sunny_talwar

If you set your environmental variable with the required date

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;

All you would need to do is this

Date(Date#(ND_DATE,'DD-MMM-YY')) as ND_DATE