topic Re: Convert numeric Date to Date in QlikView

Convert numeric Date to Date

Wed, 15 Apr 2015 11:23:58 GMT

Dears ,

how are you i hope everyone in good health , we have an JDE Oracle Database and it stored the date as a numeric(18,0) i need to convert it to date Gregorian date i try it but it not work and i read some discussion about the date and did not work smooth with me

so if can some one help me in this ,

please see this date example :

date
114305
114307
114313
114327
114336
114357
115005
115011
115021
115025

this table should be a date .

and one more thing how can i convert the date to the numeric value ?!

thanks for all

Re: Convert numeric Date to Date

Gysbert_Wassenaar — Wed, 15 Apr 2015 11:32:44 GMT

What dates are those numbers supposed to be?

Re: Convert numeric Date to Date

Wed, 15 Apr 2015 11:36:13 GMT

this should be a date .

Re: Convert numeric Date to Date

Gysbert_Wassenaar — Wed, 15 Apr 2015 12:06:01 GMT

Which date?

Re: Convert numeric Date to Date

Wed, 15 Apr 2015 12:10:13 GMT

it should be like this 114298 to 25/10/2014, ........

Re: Convert numeric Date to Date

Wed, 15 Apr 2015 12:15:48 GMT

in this case i try this

((date(yearstart(makedate((((SLTRDJ - fmod(SLTRDJ,1000)) / 1000) + 1900))) + (fmod(SLTRDJ,1000) - 1)) as Date,)

but when i need to convert a normal date to a numeric value it not converted .

Re: Convert numeric Date to Date

Anonymous — Wed, 15 Apr 2015 12:22:32 GMT

Isn't it the same question that I answered here: Re: Date & Calender

The solution was

=date(makedate(left(SLTRDJ,3)+1900) + right(SLTRDJ,3)-1)

Re: Convert numeric Date to Date

Wed, 15 Apr 2015 12:27:10 GMT

yes l know ,

but

i need to convert the normal date to numeric value with the same logic that i get the date .

what i mean is revers the the formula to get the numeric value .

because i face an issue after convert it when i try to join the date withe the other table but have normal date and i need to trust after convert the date to the numeric value is the same so can any one help me in this.

when i convert 25/10/2015 give me 41937 but in my database is 114298 ?! i don't know way ?!

Re: Convert numeric Date to Date

Anonymous — Wed, 15 Apr 2015 12:32:24 GMT

If you want the reverse operation, convert a date to the number in the same format as above, this should work:

=year(date)-1900 & (date - yearstart(date))+1

Re: Convert numeric Date to Date

Wed, 15 Apr 2015 12:39:17 GMT

thank you very much .

Re: Convert numeric Date to Date

Thu, 30 Jul 2015 08:01:39 GMT

THANKS FOR GIVEN INFORMATION