Charge and format date.

[Shaglok]

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!

[ogster1974]

Something like...

Date(Date#(Floor([Old Date Time]),'YYYY-MM-DD'),'DD-MM-YYYY') as "New Date"

[hic]

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 

[Shaglok]

Thanks! But I have dates from 1899 if I put that new field as filter, even loading only the last month of data 😞

[chriscammers]

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

[ogster1974]

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"