question

daylore avatar image
daylore asked

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.
oracledateconverttimedecimal
1 comment
10 |1200 characters needed characters left characters exceeded

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 ·
daylore avatar image
daylore answered
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
10 |1200 characters needed characters left characters exceeded

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 ·
daylore avatar image
daylore answered
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**
2 comments
10 |1200 characters needed characters left characters exceeded

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 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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