Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello.
Just a stupid question. I load data from a SQL table and there is a field with the date in this format:
2023-01-19 23:33:30
How can I charge it into Qlik with format DD-MM-YYYY? I don't need the hour.
Thanks!
Something like...
Date(Date#(Floor([Old Date Time]),'YYYY-MM-DD'),'DD-MM-YYYY') as "New Date"
You probably need to move Floor() outside the interpretation function and use the full format code:
Date(Floor(Timestamp#([Old Date Time],'YYYY-MM-DD hh:mm:ss')),'DD-MM-YYYY') as "New Date"
See also https://community.qlik.com/t5/Design/The-Date-Function/ba-p/1463157
Thanks! But I have dates from 1899 if I put that new field as filter, even loading only the last month of data 😞
Try this...
Date(Floor(Num([SQLDateField])))
//Returns M/D/YYYY or whatever the default Date format would be
Date(Floor(Num([SQLDateField])),'DD-MM-YYYY')
//Returns in custom date format
If not resolved another thing to check is your old date might be coming through as text in which case replace floor with subfield to extract the date part.
Date(Date#(subfield([Old Date Time],' ',1),'YYYY-MM-DD'),'DD-MM-YYYY') as "New Date"