topic Re: Converting date format in QlikView

Converting date format

Wed, 17 Apr 2013 11:23:44 GMT

Hi,

I have the following date format in my system = 01.01.2013

When I import this into Qlikview it comes across as a string like this = 79869598

Any ideas on how to change this a date in Qlikview?

I've used functions date and #date...

Many Thanks

Re: Converting date format

PhilippeMotillon — Wed, 17 Apr 2013 12:28:32 GMT

did you try

date#(date,'DD.MM.YYYY')

Re: Converting date format

Wed, 17 Apr 2013 12:33:47 GMT

Hi!

You are looking for which database? Perhaps the ideal would be to change the format in the language of the bank.

Rebeca

Re: Converting date format

m_woolf — Wed, 17 Apr 2013 13:40:03 GMT

In your script, is your date format set to match?

SET DateFormat='MM.DD.YYYY';

Re: Converting date format

Thu, 18 Apr 2013 14:51:00 GMT

When I use this code

date#(date(Date),'DD.MM.YYYY') as test,

Then date format comes out as this... 24.11.220904

I have also set the DateFormat to 'MM.DD.YYYY'

Any ideas?

Thanks

Re: Converting date format

Thu, 18 Apr 2013 16:04:06 GMT

Im pulling the info from an SAP system (SQL)

I've extracted dates from on this DB before and havent had any issues... this seems very strange

Re: Converting date format

Thu, 18 Apr 2013 17:00:08 GMT

HI ,

Try the below code

date#(Date,'DD.MM.YYYY') as test,

Please reply me for the same.

//Yusuf

Re: Converting date format

Fri, 19 Apr 2013 08:52:11 GMT

That still doesnt work unfortunately

I still keep getting a variation of this string whenever I convert the date - 7978785879

Re: Converting date format

Fri, 19 Apr 2013 09:57:41 GMT

Hi,

In qlikview it can be handled just need more info about your load .

Can you please attach the sample file .

//Yusuf

Re: Converting date format

calvindk — Fri, 19 Apr 2013 10:07:54 GMT

Try this:

Date(Left(Field, 2) & Mid(Field, 4, 2) & Right(Field, 4))

Re: Converting date format

Fri, 19 Apr 2013 10:33:43 GMT

Here is the attached sample

Thanks

Re: Converting date format

kaushiknsolanki — Fri, 19 Apr 2013 10:54:40 GMT

Hi,

If you look at the script in your example, the field [GDATU] is coming from your database.

Which has the values which are not in the date format and thus you are using the below expression to convert into date format

Date(Left([GDATU], 2) & Mid([GDATU], 4, 2) & Right([GDATU], 4)) as Date,

So unless the field coming from the database doesnt have the proper date format, qlikview will not be able to convert it.

Kindly check the same field in your database(SAP)

Regards,

Kaushik Solanki

Re: Converting date format

Fri, 19 Apr 2013 11:08:43 GMT

The field on my SAP table is in the following format - 01.01.2012

Re: Converting date format

kaushiknsolanki — Fri, 19 Apr 2013 11:12:11 GMT

Hi,

But the data which you gave in currtest.qvw file, the field GDATU has some different values.

Kindly check youe qvw file.

Try one more thing, instead of storing the data in QVD and then fetching it, directly get the data from Database for just knowing the values of the field GDATU.

Regards,

Kaushik Solanki

Re: Converting date format

Fri, 19 Apr 2013 11:14:39 GMT

Here you go

Thanks

Re: Converting date format

Fri, 19 Apr 2013 11:15:22 GMT

hi try this

Date(Date#('01.01.2013','DD.MM.YYYY'),'DD-MM-YYYY')

Re: Converting date format

kaushiknsolanki — Fri, 19 Apr 2013 11:24:14 GMT

Hi,

Correct if you see the date field, it is not in the form which you said (01.01.2012) its into something else format.

So this is not a qlikview issue, you need to check with your database guy to tell you what format SAP is using.

Regards,

Kaushik Solanki

Re: Converting date format

Fri, 19 Apr 2013 11:25:16 GMT

Thanks for your help

Re: Converting date format

Fri, 19 Apr 2013 11:36:37 GMT

HI ,

Can you provide what is the Date in the SAP for the GDATU value:79869598

If you provide what is Date in DB for this value i can make out .

Please reply soon.

//Yusuf

Re: Converting date format

Fri, 19 Apr 2013 11:45:29 GMT

OK...

SAP field - 01.03.2013

Qlikview output - 79869698

Thanks