Converting an int to a datetime

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 2000 Forums

Transact-SQL (2000)

Converting an int to a datetime

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-02 : 11:03:12

Kat writes "Hello,

How do you convert an INT (such as 124155) to a DATETIME?

That is, 124155 = 12:41 pm and 55 seconds.

How do I convert this to just the TIME portion of the datetime?

So far:

DECLARE @myval int
SET @myval = 124155
SELECT CAST(@myval AS datetime)

Yields:
2239-12-05 00:00:00.000

Which is obviously wrong. I don't know if using DATEPART would help, but somehow I don't think so.

If anyone can guide me, very appreciated!

Thanks :-)"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-02 : 11:11:48

Why do you want this conversion?
You have to use proper datatype to store the data

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 11:23:56

Welcome to SQL Team Kat!


DECLARE @myval int
SET @myval = 124155
DECLARE @TempString varchar(8)
SELECT @TempString = RIGHT('000000' + CONVERT(varchar(8), @myval), 6),
	@TempString = LEFT(@TempString, 2) + ':' + SUBSTRING(@TempString, 3, 2) + ':' + RIGHT(@TempString, 2)
SELECT CAST(@TempString AS datetime)

EDIT: Added fix for less-than 6 digit dates
Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-03 : 01:38:49

Kristen, this will work only if the value has six digits

>>How do I convert this to just the TIME portion of the datetime?

What date do you want it to be part of?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-03 : 04:49:39

"this will work only if the value has six digits"

Good catch Mad.

I should have also added that the "time" will be made part of "01-Jan-1900", this is how SQL chooses to store just-time at this moment - but, for example, if you have two such "time" records DIFFERENCE etc. will work just fine

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources