Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
jisephcirspy
Contributor III
Contributor III

Change date format from 'YYYY-MM-DD HH:mm:ss' to 'DD.MM.YYYY'

Hello,

I have a date field and I want to change the format from 'YYYY-MM-DD HH:mm:ss' to 'DD-MM-YYYY', like:

2020-09-23 00:00:00 to 23.09.2020

Labels (4)
3 Replies
bhavyagrb
Partner
Partner

Hello,

Please try below expression,hope this helps you!!

date(date#([DateField],'YYYY-MM-DD HH:mm:ss'),'DD-MM-YYYY')

or

date(date#([DateField],'YYYY-MM-DD HH:mm:ss'),'DD.MM.YYYY')

 

Regards,

Bhavya

tresesco
MVP
MVP

@jisephcirspy If your date field is a proper date type (number at the back) field, you could simply use date(), like:

Date(DateField,'DD-MM-YYYY')

If it is a text field, you have to parse the data using parsing functions like date#() and wrap it with date(), like:

Date(Date#(DateField,  'YYYY-MM-DD hh:mm:ss' ),'DD-MM-YYYY')

You can also think of truncating the unnecessary timestamp using floor(), like:

Date(Floor(DateField),'DD-MM-YYYY')

Or,

Date(Floor(Date#(DateField,  'YYYY-MM-DD hh:mm:ss' )),'DD-MM-YYYY')

 

Vegar
MVP
MVP

If you have more than one time registred per day you will need to use Floor as @tresesco  suggests. Take alook at the image and script below and you will notice that not using floor will give you multiple instances of field values that looks the same, but have different undelying numeric values.

LOAD 
date(floor(timestamp#("My timestamp",'YYYY-MM-DD HH:mm:ss')),'DD-MM-YYYY') as "My date",
date(timestamp#("My timestamp",'YYYY-MM-DD HH:mm:ss'),'DD-MM-YYYY') as "My date (not floored)",
"My timestamp"
inline [
My timestamp
2020-09-23 09:52:00
2020-09-23 13:52:22
2020-09-22 09:52:00
2020-09-21 03:02:30
2020-09-21 03:01:00
2020-09-21 13:09:00
];

Notice that the not floored date field have multiple fieldvalues that looks the same, but have different underlying numeric values.Notice that the not floored date field have multiple fieldvalues that looks the same, but have different underlying numeric values.

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes