Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Anita,
Try following dimensions which solve your qurey.
date(DateReady,'MM-DD-YYYY')
Best,
DP
This results in an empty value ... so doesn't work.
Did try a lot of different options but none of them seems to work 😞
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!!
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....
what are the date format in your source data?
can you give some of rows from your file with dates?
hi try this
Date(date#(Sdate,'MM\DD\YYYY') as SourceDate
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
Please find attached Excel file including data