# question

## Decimal to Time

SELECT date_created FROM table gives me columns of data like this: 1312912118 So I did this: SELECT (date_created/86400)+25569 FROM table gives me date like this 40323.5705092592 How can I convert either the 10-digit # or the decimal # to DD/MM/YYYY HH:MM:SS AM/PM? I'm new to SQL so go easy hehehe. Took me 2 days to figure out the 10-digit to decimal conversion. I know how to convert the data in excel, but trying to convert it with the query instead. There are many columns with this type of problem. So i'm not looking to convert 1 field. Wanting to convert the whole table via query.
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
Can you confirm that the RDBMS you're working with is SQL Server? Can you also tell us in the example above, what the expected date and time should be?
0 Likes 0 ·

·
I think i got it! to_char(to_date('01-JAN-1970','DD-MON-YYYY') + ( DATE_CREATED/ (60 * 60 * 24) ),'MM-DD-YYYY HH:MI:SS AM') I have many date/time fields I need to do this to. I guess I copy/paste this formula and for each field, rename 'DATE_Created' to whatever column I have?
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
I would assume so. Glad that you found a solution.
0 Likes 0 ·
The database i'm connecting to is a Remedy ticketing system, with a back-end Oracle interface. I'm connecting to it via software called SQL Developer. **1312912118** or **40323.5705092592** equates to **5/25/2010 1:41:32 PM**

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
Have you tried something like select to_date(date_created,'yyyymmdd') FROM Table1? NOTE: I'm not an Oracle guy at all so I'm just guessing. http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm Is the data stored as a string or what is the data type?
1 Like 1 ·
·
Thanks for the extra info. I've retagged as Oracle.
0 Likes 0 ·