Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Date formatting

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

7 Replies
Highlighted
Partner
Partner

Re: Date formatting

You can try something like this.

264980.PNG

Good Luck

Oscar

Highlighted
Creator
Creator

Re: Date formatting

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

Highlighted
Partner
Partner

Re: Date formatting

What format is the Date in?  MDY or DMY?

Highlighted
Partner
Partner

Re: Date formatting

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

264980-1.PNG

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

Highlighted

Re: Date formatting

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);

Highlighted
Creator II
Creator II

Re: Date formatting

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);

Highlighted
Partner
Partner

Re: Date formatting

Hi,

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

Get the Dates Right

Cheers,

Daniel