Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Try it
Date#(yourdatefield,'DD/MM/YYYY')
like
Date#(date,'DD/MM/YYYY')
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
Hi Shaila,
date(date#(DateField,'MM/DD/YYYY hh:mm'),'MM/DD/YYYY') AS Date
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.
can you post sample data source
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
Hi Ramya,
Treat it as a string and then use make date and create a date.
thanks,
Rajesh Vaswani
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
Try to treat it like a string. If possible go for something like:
makedate(right(DateField,4),mid(DateField,bla,bla) )
BR
Serhan