convert text M\D\YYYY to date field DD-MM-YYYY -- am I missing something??

[Anonymous]

am I missing something??

I have a date field which I load from a CSV file ... in the format M\D\YYYY e.g. 3/10./2010
I want to convert this text field into a date field with the format DD-MM-YYYY.

In the load script I do the following:

...
 date(date#(DateReady,'M\D\YYYY')) as Combi.Date,
...

The result is an empty date ...

when only doing

date#(DateReady,'M\D\YYYY')

I get the resulting 3/10/2010

What am I doing wrong?? In other cases we did the same thing and that seems to work.

Anita

[sparur · Accepted solution]

Hello, Anita

what format in your source?

MM\DD\YYYY e.g. 3\10\2010

or

MM/DD/YYYY e.g. 3/10./2010

?

for first, try: Date(date#(DateReady,'MM\DD\YYYY'), 'DD-MM-YYYY') as DateReady

for second, Date(date#(DateReady,'MM/DD/YYYY'), 'DD-MM-YYYY') as DateReady

[#3]

Hi Anita,

Try following dimensions which solve your qurey.

date(DateReady,'MM-DD-YYYY')

Best,

DP

[Anonymous]

This results in an empty value ... so doesn't work.

Did try a lot of different options but none of them seems to work 😞

[Anonymous]

mmm strangely the MM/DD/YYYY thing worked ...

Strange that the other formatting did work half way ...

can't explain why

date(date#(Combi.DatumKlaargezet,'M\D\YYYY')) as Combi.Datum1

results in a half way manner ...

But thx ... can continue on this :D!!

[sparur]

so, try to parse fields manually:

LOAD

date(Makedate(year, month, day), 'DD-MM-YYYY') as FormatDate

;

LOAD

DateReady,

Left( DateReady, 2) as month,

mid( DateReady, 4,2) as day,

right( DateReady, 4) as year

FROM....

[sparur]

what are the date format in your source data?

can you give some of rows from your file with dates?

[ashfaq_haseeb]

hi try this

Date(date#(Sdate,'MM\DD\YYYY') as SourceDate

[jpaviles1]

Hello Qlik Community,

It seems dates are reported as string  M/D/YYYY on Capture1

When trying Date#("Timestamp",'DD/MM/YYYY') as "Timestamp1" on the Data Load Editor, I get result as per Capture2.

How to get date range properly reported from 09/12/2023 to 08/01/2024?

Thank you,

Jose

[jpaviles1]

Please find attached Excel file including data