topic Re: Date and Time format in QlikView

Date and Time format

cmutombo — Sun, 15 Aug 2021 11:48:42 GMT

Hi Community

Please help

This below date :

2021-08-14 00:00:00.000000 is at which format ??

If I want to explain it to format : '/M/DD/YYYY' for ex

Re: Date and Time format

Taoufiq_Zarra — Sun, 15 Aug 2021 12:10:43 GMT

@cmutombo if you want this format : /8/14/2021

first on top you can change DateFormat to SET DateFormat='/M/DD/YYYY';

then if you you can use Date#() to transforme you date

if you have a problem with Date# you can use this version:

makedate(subfield(Date,'-',1),subfield(Date,'-',2),left(subfield(Date,'-',3),2))

like this :

LOAD *,makedate(subfield(Date,'-',1),subfield(Date,'-',2),left(subfield(Date,'-',3),2)) as New_Date INLINE [ Date 2021-08-14 00:00:00.000000 ]

output:

or with Month() and Year(),...

Re: Date and Time format

cmutombo — Sun, 15 Aug 2021 12:42:22 GMT

PREVIEW

Re: Date and Time format

Taoufiq_Zarra — Sun, 15 Aug 2021 12:47:25 GMT

Maye be you can use it like

=date(max(floor(Timestamp#([GROSS_ADDS.DATE_],'YYYY-MM-DD h:mm:ss[.fff] TT'))) ,'M/DD/YYYY')

or you can share a sample data to check

Re: Date and Time format

cmutombo — Sun, 15 Aug 2021 12:52:03 GMT

=date(max(floor(Timestamp#([GROSS_ADDS.DATE_],'YYYY-MM-DD h:mm:ss[.fff] TT'))) ,'M/DD/YYYY')

give No result...

Re: Date and Time format

cmutombo — Sun, 15 Aug 2021 12:58:23 GMT

Let me tell you the story.

I come from this formula :

=sum({<[GROSS_ADDS.DATE_]={"$(=max([GROSS_ADDS.DATE_]))"}>}[GROSS_ADDS.SUBS]) IS WORK CORRECTY

Now I want to take the day before max([GROSS_ADDS.DATE_], Kind of Date(max([GROSS_ADDS.DATE_]-1)

The issue is Date(max([GROSS_ADDS.DATE_]-1) give : 08/15/2021 then, my sum is blank because [GROSS_ADDS.DATE_] is at format 2021-08-14 00:00:00.000000

Re: Date and Time format

cmutombo — Sun, 15 Aug 2021 13:13:04 GMT

Let me tell you where I come from :

I used this formula :

1. =sum({<[GROSS_ADDS.DATE_]={"$(=max([GROSS_ADDS.DATE_]))"}>}[GROSS_ADDS.SUBS]).. Work CORRECTLY

2. I want same SUM at they before max([GROSS_ADDS.DATE_], then Date(max([GROSS_ADDS.DATE_]-1)

3. The issue is Date(max([GROSS_ADDS.DATE_]-1) is out on format 8/14/2021 and [GROSS_ADDS.DATE_] is at format 2021-08-14 00:00:00.000000

4. How to convert Date(max([GROSS_ADDS.DATE_]-1) at format : 2021-08-14 00:00:00.000000

Re: Date and Time format

MayilVahanan — Mon, 16 Aug 2021 03:32:04 GMT

Hi @cmutombo

Based on ur sample, max([GROSS_ADDS.DATE_]) <- returns " 2021-08-15 00:00:00.000000",

so try like below

Date(Date#(Left(max([GROSS_ADDS.DATE_]), 10),'YYYY-MM-DD'), 'MM/DD/YYYY')

Date(Left(max([GROSS_ADDS.DATE_]), 10), 'MM/DD/YYYY')

Re: Date and Time format

cmutombo — Mon, 16 Aug 2021 07:24:32 GMT

Hi MayilVahanan

YOU ARE RIGHT ! Thanks a lot

Re: Date and Time format

MayilVahanan — Mon, 16 Aug 2021 07:32:14 GMT

Please close the thread and its helpful for others to refer the right answer in future for same kind of scenario.

Re: Date and Time format

Kushal_Chawda — Mon, 16 Aug 2021 11:20:23 GMT

@cmutombo if max([GROSS_ADDS.DATE_]) return any value then you can simply use below

date(floor(max([GROSS_ADDS.DATE_])),'M/DD/YYYY')