topic Re: Date Conversion Not Working in App Development

Date Conversion Not Working

kateevans2 — Fri, 11 Jan 2019 11:46:54 GMT

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

Re: Date Conversion Not Working

sunny_talwar — Fri, 11 Jan 2019 12:12:16 GMT

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);

Re: Date Conversion Not Working

ishanbhatt — Fri, 11 Jan 2019 12:21:18 GMT

Hello,

Use below formula.

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

Set Date format in the start.

Re: Date Conversion Not Working

kateevans2 — Fri, 11 Jan 2019 12:23:00 GMT

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

Re: Date Conversion Not Working

sunny_talwar — Fri, 11 Jan 2019 12:25:02 GMT

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