topic Re: Extracting date from date & time in QlikView

Extracting date from date & time

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I am new to Qlikview and hope to get some guidance.

I have a dimension datetime that looks like this:

Format:DD/MM/YYYY HH:MM

8/17/2015 7:05

15/17/2015 19:05

How could I extract the date DD/MM/YYYY from it?

Re: Extracting date from date & time

sunny_talwar — Wed, 12 Jul 2017 13:59:55 GMT

15/17/2015? What kind of date is this? Typo? May be try this

Date(Floor(TimeStamp#(DateField, 'DD/MM/YYYY hh:mm')), 'DD/MM/YYYY') as Date

Re: Extracting date from date & time

Chanty4u — Wed, 12 Jul 2017 14:15:17 GMT

Hi try this

LOAD * Inline

[

Dates

8/17/2015 7:05

12/17/2015 19:05

];

Result:

load *,

Date(Date#(Dates,'M/DD/YYYY hh:mm'),'DD/MM/YYYY') as NewDate

Resident a;

Re: Extracting date from date & time

Wed, 12 Jul 2017 14:55:11 GMT

Hi chanty4u‌,

I tried your method and it works great. Thanks for helping.

Re: Extracting date from date & time

Wed, 12 Jul 2017 14:56:16 GMT

Apologies, i made a typo in the 2nd date. Thanks for your help Sunny.

Re: Extracting date from date & time

sunny_talwar — Wed, 12 Jul 2017 15:08:09 GMT

I would suggest you not to use this without a Floor function.... because if you don't add Floor, the date might look like just the date, but the underlying value is still a timestamp. What that means is that if you have more than one timestamp for a single day, those will repeat in the list box.... Use this

Date(Floor(TimeStamp#(DateField, 'DD/MM/YYYY hh:mm')), 'DD/MM/YYYY') as Date

or this based on your timestamp format

Date(Floor(TimeStamp#(DateField, 'M/D/YYYY hh:mm')), 'DD/MM/YYYY') as Date

Re: Extracting date from date & time

Anonymous — Wed, 12 Jul 2017 15:13:31 GMT

Agree with Sunny. I think you can also use the DayStart function.

Re: Extracting date from date & time

sunny_talwar — Wed, 12 Jul 2017 15:16:51 GMT

Or even DayName()

dayname ‒ QlikView