convert long int to date 1

[wgechter]

I have a field d_chart_open, type number(11) that has values such as 55666, 55687, etc. I would like those numbers to be displayed as a date, May 29, 02 or 5/29/02 but am not sure the formula I should use. All dates are based off the date 12/31/1849. I am using Oracle SQL Plus. Thanks, Wendi

 

[nagornyi]

to_date('31-DEC-1849')+d_chart_open

 

[javydreamercsw]

I tried this answer but didn't work, what I've found is that in my case the type is long(4). What modifications shall be done? Values are like : 1114582520

 

[nagornyi]

I doubt the numbers like that denote days passed from some base date. Most likely they mean seconds. So devide your numbers by number of seconds in 24 hours (24*60*60) and add to your base date.

 

[javydreamercsw]

Is there a way to getbase date?

 

[javydreamercsw]

And by the way how I could do that calculation in the sql statement?

 

[nagornyi]

The base date should be in the DB description. It can be 01-JAN-1900, 01-JAN-1950, or anythin else. To make calculations, just use
...to_date('01-JAN-1900')+ your_long4_field/86400

 

[javydreamercsw]

Thanks, the answer is:

SELECT to_date('1-JAN-1970')+F_SGTM/(24*60*60) FROM SGRP_EXT

Figured it out just a couple of mins before I got your email but thanks anyways!