Date & Calender

[#1]

Dears,

Good day,

I hope every one in good health , and i need to know something about date if the date stored in our database as a number like this(115001,115002,115003,.......,,115046)

how we can convert it to date (year ,month , day ....) what is the formula & logically how it work .

Ahmad Kastero

Thanks For All

[sunny_talwar]

What date does 115001 corresponds to?

Best,

Sunny

[sunny_talwar]

If this is a number format then it corresponds to 11/10/2214. So my guess is, this is not a number format date you have stored in your database.

[#4]

this is a number format ( in the application ERP it is a date but when store it in the database it convert it to number )

[#5]

some friend solve it and convert it by using this ((date(yearstart(makedate((((SLTRDJ - fmod(SLTRDJ,1000)) / 1000) + 1900))) + (fmod(SLTRDJ,1000) - 1)) as Date,) but know he leave the company and i tray to understand it , this code already return the date . but how it work ?! 

[oknotsen]

Qlik stores dates as numbers.

1 = December 31st 1899

Today (April 6th 2015) = 42100

If your system is also storing dates as numbers, but with a different starting point, calculate the different and subtract that from your number.

[sunny_talwar]

So 115001 seems to equal 1/1/2015 according to your formula. Is that correct?

Best,

Sunny

[Anonymous · Accepted solution]

I see - the first three digits (115) is the number of years after 1900.  That is 115 is 1900+115= 2015

The last three digits (001) is the number of day in the year.  So, a simpler version of expression is:

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

[maleksafa]

this is julian date format, what is the database that you are using? because in my case we also have this date format in sql server database, and what i am doing is converting this date to datetime when extracting the data from sql.

[mjayachandran]

Its called JD Edwards date format very similar to Julian Date.

If you want to understand what it is and how the converstion is done please go throught the link below:

JD Edwards Date Conversions (CYYDDD) | Kirix Strata Blog

you can use a simple formula in excel to convert it to readable format using :

=DATE(1900+(A1/1000),1,1)+RIGHT(A1,3)-1

in Qlikview it is by using

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