Convert int(time) to datetime - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Convert int(time) to datetime

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-06 : 14:28:09

I have a database that stores time mostly in 5 digit numbers such as 57240 or 34260 or 42840, etc. Does anyone have any idea what this translates to in real time and how to do it in SQL?

Duane

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-08-06 : 14:55:36

the INT part of a SQL DATETIME value is the number of days since Jan 1, 1900. I would say that the easiest way to translate the dates would be to

PRINT CAST(57240 AS DATETIME)

assuming the INT value is created in the same way. Conversion from INT to DATETIME in this case can be done implicitly, although you might get a performance hit that way.

UNIX timestamps use int values to represent the number of seconds from the start of the current epoch (which I believe started Jan 1 1970) which would be converted as such:

PRINT DATEADD(ss, 57240, '1970 Jan 01')

This is unlikely to be your case however since 5 digits wouldn't let you get out of the first week in January 1970.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-06 : 15:13:04

Thank you immensely for this. I have been struggling for hours to find a datetime column through the whole database and this is the actual statement I created to get the appointment datetime:

SELECT *, DATEADD(ss, Start_Time, Date_Provided) Appt_Time
FROM SYS_SKED_CONFLICT where Resource_Id = @Resource_Id

Start_Time was the 5 digit number mentioned above, Date_Provided was the Appt_Time and Resource_ID was the ID of the person with the appointment.

Duane

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-08-06 : 15:20:19

I'm unclear from your response, is your problem solved, or is it more complicated? :)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-06 : 15:22:42

Not sure that's correct. Do these dates seem right for these ints (57240, 34260, 42840):

2056-09-19 00:00:00.000
1993-10-20 00:00:00.000
2017-04-17 00:00:00.000

Be One with the Optimizer
TG

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-08-06 : 15:44:41

That's why I was saying it was dependent on the system it was coming from. If it was a straight conversion using a standard convention for creating dates, these are accurate dates; if not... well, it gets a great deal more problematic. I really don't know the context to say if this is accurate or not.

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-06 : 15:46:38

Oh, I'm sorry - the problem is solved. I was confused about that 2056 stuff, too. But the DateAdd function used in conjunction with this solved the mystery. The date itself was in one column (Date_Provided), ie: 2009-07-20 00:00:00.000. The 57240 was was in the Start_Time column as an int. When I added them together with the DateAdd function, I got 2009-07-24 15:54:00.000, which was the actual appointment time, so I put that in a calculated column called Appt_Time. Thank you for all the input here. I know this is an oddball time, but I verified it in the application.

Duane

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-08-07 : 10:23:58

glad I could help.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2009-08-07 : 10:25:47

By the way Duane, you have a nice name :)

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources