Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Specialist
Specialist

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

ECG line chart is the most important visualization in your life.

View solution in original post

11 Replies
tyagishaila
Specialist
Specialist

Try it

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

like

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

its_anandrjs

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

Anonymous
Not applicable
Author

Hi Shaila,


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







Anonymous
Not applicable
Author

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
Master III
Master III

can you post sample data source

mato32188
Specialist
Specialist

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

ECG line chart is the most important visualization in your life.
rajeshvaswani77
Specialist III
Specialist III

Hi Ramya,

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

thanks,

Rajesh Vaswani

Not applicable
Author

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

Anonymous
Not applicable
Author

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

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

BR

Serhan