Convert Date format

[#1]

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.

[tyagishaila]

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]

Hi Shaila,

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

[Anonymous]

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]

can you post sample data source

[mato32188 · Accepted solution]

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]

Hi Ramya,

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

thanks,

Rajesh Vaswani

[#9]

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]

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

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

BR

Serhan