topic Re: About date format in QlikView

About date format

lionking15 — Wed, 25 Nov 2020 16:16:04 GMT

hi,

i have create a date column in my excel sheet and given date as 19/07/2016 but after reloading the script it is coming in number format 42570 why??and how to correct it??

Re: About date format

oknotsen — Fri, 22 Jul 2016 07:22:51 GMT

Dates are actually numbers (counted from Dec 31st, 1899).

If you put a Date() function around that field in your load script, all problems should be solved.

Normally dates just stay dates, so my guess is you are also loading that same field name as a number in your script or you use it in a mapping load, as mapping tables turn dates into numbers again.

Re: About date format

qlikviewalex — Fri, 22 Jul 2016 07:24:07 GMT

If you load the Value. You can ad Date#()

load

...

date#( yourDate ) as yourDate

Re: About date format

PrashantSangle — Fri, 22 Jul 2016 07:24:40 GMT

Hi,

just use date(dateField,'DD/MM/YYYY')

Regards

Re: About date format

antoniotiman — Fri, 22 Jul 2016 07:24:57 GMT

Use

Date(DateField) as DateField

Date Field is Dual Field

Text -> 19/07/2016 and Number -> 42570

Regards

Re: About date format

oknotsen — Fri, 22 Jul 2016 07:28:17 GMT

Correction: Without the #

Re: About date format

MindaugasBacius — Fri, 22 Jul 2016 07:37:48 GMT

Date have both number and string interpretation.

From Reference Manual:

From QlikView Help:

Re: About date format

oknotsen — Fri, 22 Jul 2016 08:06:25 GMT

Note that help is now (since QlikView 12) completely online, so there is no need to post screenshots of it. You can just link to the appropriate topic.

Qlik | Help

Re: About date format

MindaugasBacius — Fri, 22 Jul 2016 08:11:58 GMT

Thanks for that!