Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Convert Date format


Hi,

I have a date column in my sheet which is in "Mon Nov 23 08:22:02 CST 2001" format.

I want to convert and display it in DD/MM/YYYY format.

1 Solution

Accepted Solutions
mato32188
Contributor III

Re: Convert Date format

Hi Ramya,

date(date#(left(mid(Date, 9), 2) & '/' & left(mid(Date, 5), 3) & '/' &  Right(Date, 4),'DD/MMM/YYYY' ), 'DD/MM/YYYY') as %date

and dont forget to

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='D. M. YYYY h:mm:ss[.fff]';

SET MonthNames='1;2;3;4;5;6;7;8;9;10;11;12';


BR

Martin

11 Replies
tyagishaila
Valued Contributor

Re: Convert Date format

Try it

Date#(yourdatefield,'DD/MM/YYYY')

like

Date#(date,'DD/MM/YYYY')

Re: Convert Date format

Hi,

If your date is in the pure date format then try with

=Date( DateField,'DD/MM/YYYY')

And

Load

Date( DateField,'DD/MM/YYYY' ) as TestDate

From Location;

Regards

Anand

neetha_p
Honored Contributor

Re: Convert Date format

Hi Shaila,


date(date#(DateField,'MM/DD/YYYY hh:mm'),'MM/DD/YYYY')  AS Date







Highlighted
Employee
Employee

Re: Convert Date format

Hi Ramya,

I would start by using the SubField command to get to the individual parts of the string and put them into a date format.

SubField(date, ' ', 3) as day,

SubField(date, ' ', 6) as year,

SubField(date, ' ', 2) as month,

You can then use these to put together your date using either a Load on Load or a resident load

Date#(day & '/' & month & '/' & year, 'DD/MMM/YYYY') as NewDate

Dates are always a bit fiddly but you have the data available, you just have to throw it around until you get what you want. There might be a more efficient way but this should work.

arulsettu
Honored Contributor III

Re: Convert Date format

can you post sample data source

mato32188
Contributor III

Re: Convert Date format

Hi Ramya,

date(date#(left(mid(Date, 9), 2) & '/' & left(mid(Date, 5), 3) & '/' &  Right(Date, 4),'DD/MMM/YYYY' ), 'DD/MM/YYYY') as %date

and dont forget to

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='D. M. YYYY h:mm:ss[.fff]';

SET MonthNames='1;2;3;4;5;6;7;8;9;10;11;12';


BR

Martin

rajeshvaswani77
Valued Contributor III

Re: Convert Date format

Hi Ramya,

Treat it as a string and then use make date and create a date.

thanks,

Rajesh Vaswani

Not applicable

Re: Convert Date format

the best thing you can do is...

using all columns create a VIEW in data base and do the amendments in the database it self

and LOAD SQL in qlikview and try queries

Partner
Partner

Re: Convert Date format

Try to treat it like a string. If possible go for something like:

makedate(right(DateField,4),mid(DateField,bla,bla) )

BR

Serhan