Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Time Field

Author  Topic 

velvet_tiger
Starting Member

12 Posts

Posted - 2014-10-01 : 14:57:01
Hi All,

Has anyone in here ever came across a time field (only stores the time i.e. 10:30am etc) that has the time recorded as 6600, 10800 etc?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-01 : 15:10:18
What data type is it using?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

velvet_tiger
Starting Member

12 Posts

Posted - 2014-10-01 : 15:17:24
It is a numeric field.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-01 : 15:20:11
You'll need to investigate the application or stored proc that is doing the insert/update. But I would highly recommended using time data type instead as this issue would not occur with it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

velvet_tiger
Starting Member

12 Posts

Posted - 2014-10-01 : 15:21:20
Ultimately I would like to convert this into a more recognizable format.
Go to Top of Page

velvet_tiger
Starting Member

12 Posts

Posted - 2014-10-01 : 16:12:03
Hi All,

The numeric field represented the UNIX epoch time. I used the following code to convert it to a time field

to_char(to_date(TIMEFIELD,'sssss'),'hh24:mi:ss')

Thank you for your help tkizer.
Go to Top of Page
   

- Advertisement -