Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Convert numeric Date to Date

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

1 Solution

Accepted Solutions
mov
Champion III
Champion III

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

View solution in original post

10 Replies
Gysbert_Wassenaar

What dates are those numbers supposed to be?


talk is cheap, supply exceeds demand
Not applicable
Author

this should be a date .

Gysbert_Wassenaar

Which date?


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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 .

mov
Champion III
Champion III

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)

Not applicable
Author

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 ?!


mov
Champion III
Champion III

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

View solution in original post

Not applicable
Author

thank you very much .