topic Convert Date format in QlikView

Convert Date format

Thu, 11 Dec 2014 10:29:34 GMT

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.

Re: Convert Date format

tyagishaila — Thu, 11 Dec 2014 10:31:43 GMT

Try it

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

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

Re: Convert Date format

its_anandrjs — Thu, 11 Dec 2014 10:36:02 GMT

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

Re: Convert Date format

Anonymous — Thu, 11 Dec 2014 10:37:16 GMT

Hi Shaila,

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

Re: Convert Date format

Anonymous — Thu, 11 Dec 2014 10:40:44 GMT

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.

Re: Convert Date format

arulsettu — Thu, 11 Dec 2014 10:41:16 GMT

can you post sample data source

Re: Convert Date format

mato32188 — Thu, 11 Dec 2014 10:55:58 GMT

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';

Martin

Re: Convert Date format

rajeshvaswani77 — Thu, 11 Dec 2014 11:22:24 GMT

Hi Ramya,

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

thanks,

Rajesh Vaswani

Re: Convert Date format

Thu, 11 Dec 2014 11:30:30 GMT

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

Re: Convert Date format

Anonymous — Thu, 11 Dec 2014 11:49:19 GMT

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

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

Serhan

Re: Convert Date format

SunilChauhan — Fri, 12 Dec 2014 11:13:37 GMT

its need to break in date Month and Year

simpaly create date using

MakeDate(Right(Datefield,4),

Month(Date(Date#(Mid(Datefield,5,3),'MMM'),'MM')),

Mid(Datefield,8,3))

event you could format this using

Date(MakeDate(Right(Datefield,4),

Month(Date(Date#(Mid(Datefield,5,3),'MMM'),'MM')),

Mid(Datefield,8,3)),'Format')

here format is dd/MM/YY or DD-MM-YYY or YYYY-MM-DD or DD-MMM-YYYY

as per your convineyance

Re: Convert Date format

MarcoWedel — Fri, 12 Dec 2014 11:40:30 GMT

=Date(Floor(Timestamp#(Replace(Mid('Mon Nov 23 08:22:02 CST 2001',5),'CST ',''),'MMM DD hh:mm:ss YYYY')),'DD/MM/YYYY')

for the date part, or

=Timestamp(Timestamp#(Replace(Mid('Mon Nov 23 08:22:02 CST 2001',5),'CST ',''),'MMM DD hh:mm:ss YYYY'))

for a complete timestamp.