Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
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

You can try something like this.

264980.PNG

Good Luck

Oscar

Highlighted
Creator
Creator

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

Highlighted
Partner
Partner

What format is the Date in?  MDY or DMY?

Highlighted
Partner
Partner

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
MVP
MVP

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

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

Hi,

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

Get the Dates Right

Cheers,

Daniel