format field into a date


I have a field in sql server 2008 which is in seconds ie 1360146299 I believe its working from 1904 forwards.

how can I alter this to show a date more understandable in t-sql

Who is Participating?
PortletPaulConnect With a Mentor Commented:
try this perhaps using the unix epoch date

  DATEADD(second, 1360146299, '19700101')
, convert(varchar, DATEADD(second, 1360146299, '19700101') ,121)

= 2013-02-06 10:24:59.000
do you know what 1360146299 should represent in more conventional yyyy-mm-dd terms?
(or any other similar values to dates that you know are a particular date)
dsackerConnect With a Mentor Contract ERP Admin/ConsultantCommented:
Sure it's not starting from 1970? If I add these seconds to 1970, I get a date within 2013. However, you can take the following and work it however you wish:

SELECT  DATEADD(s, 1360146299, '1/1/1970')

Result:  2013-02-06 10:24:59.000

If you start from 1904, you get this:

SELECT  DATEADD(s, 1360146299, '1/1/1904')

Result:  1947-02-06 10:24:59.000
DarrenJacksonAuthor Commented:
Sorry 1970 is more likely

Both answers help so I will split

Thank you for a quick response
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.