topic Date conversion giving wrong values in QlikView

Date conversion giving wrong values

Sun, 07 Nov 2010 05:16:34 GMT

Hi,

I would really appreciate help with this date issue.

i have a datefield Field1 = 20100701

if i use month(Field1) as Month then Month = 'Nov' and this is wrong it is supposed to be Jul

and when i want to get the year is use year(Field1) and i get 56933

what am i missing ?

Thank you.

Sun, 07 Nov 2010 05:36:13 GMT

I need to add the way i get to field1 date field is

19000000 + field1 as field1

thks

Sun, 07 Nov 2010 08:56:24 GMT

You need to transform your field in the script as

date(date#(FIELD1,'YYYYMMDD'),'YYYY/MM/DD') as field1

this is going to transform the original with the format YYYYMMDD, , so you can use year(), month() etc..

JJJ

etay_netintent — Sun, 07 Nov 2010 10:19:24 GMT

Another way to do the same is to use MakeDate(Year,Month,Day)

MakeDate(Left(FIELD1,4), Mid(FIELD1, 5,2), Right(FIELD1,2) AS Date

What is the default fate format in the script settings ?? You may try to set it to YYYYMMDD, and then use a simple Date(FIELD1).

Sun, 07 Nov 2010 15:03:11 GMT

Jean-Jacques Jesua thanks so much it worked 100% for me.