topic Re: Date formatting in QlikView

Date formatting

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

I have the date format as in attached file.

I ma trying to change the format ,but no results.

Using the below expression -

DATE(DATE#(Date,'MM/DD/YYYY')) as Date

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

any help please.,Thank you

Re: Date formatting

oscar_ortiz — Wed, 21 Jun 2017 18:48:12 GMT

You can try something like this.

Good Luck

Oscar

Re: Date formatting

apthansh — Wed, 21 Jun 2017 19:27:17 GMT

PFA file ....something is wrong...

Re: Date formatting

oscar_ortiz — Wed, 21 Jun 2017 19:53:59 GMT

What format is the Date in? MDY or DMY?

Re: Date formatting

oscar_ortiz — Wed, 21 Jun 2017 20:42:00 GMT

Depending upon which format your original date is you could do something like this.

LOAD

Reference,

Project,

Note,

[Previous Note],

Author,

Date,

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

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

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

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

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

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

[Client name]

FROM

[Date Test (1).csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Good Luck

Oscar

Re: Date formatting

Kushal_Chawda — Thu, 22 Jun 2017 07:23:50 GMT

set your timestamp variable format

SET TimestampFormat='M/D/YYYY h:mm';

Now load your csv

Data:

LOAD Reference,

Project,

Note,

[Previous Note],

Author,

Date,

[Client name]

FROM

[Date Test.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Date formatting

abc_18 — Thu, 22 Jun 2017 07:35:08 GMT

Try like this

LOAD date(Date#(Date,'MM/DD/YYYY'),'YYYY-MM-DD') as Date

FROM

(txt, codepage is 1252, embedded labels, delimiter is ' ', msq);

Re: Date formatting

dberkesacn — Thu, 22 Jun 2017 07:48:36 GMT

Hi,

this would be useful for you, check the pdf as well.

Get the Dates Right

Cheers,

Daniel