Time as Integer - 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 2000 Forums

Transact-SQL (2000)

Time as Integer

Author

Topic

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-11-28 : 23:27:05

I have a database field with the Data Type of Integer. It represents the time a transactiion occurred as follows.

4:15:00 PM would be represented as 161500 (16 = hh, 15=n, 00 = ss)

What would be the most effective means of converting this. I will be using a function.

Thanks,

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-11-28 : 23:36:13

Hey TSQLMan

It depends what you want to do...Is there a date associated - or just a time? And how will you be using it once its converted? to calculate the number of minutes it took or something similar?

Frankly, I'd just CONVERT it to a varchar, then break up each part using SUBSTRING, then concatenate these back into a varchar "time", then CONVERT the final concatenation back to a datetime eg

 select convert(datetime, substring(convert(varchar(6),[Field1]),0,2) 
+ ':' + substring(convert(varchar(6),[Field1]),2,2) 
+ ':' + substring(convert(varchar(6),[Field1]),4,2),XXX) (double check the syntax in BOL)

where XXX is the appropriate conversion type (see CONVERT in BOL- I don't have it in front of me)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-11-29 : 20:46:59

The field is not always the same length, if the time is from 1AM to 9AM it is one character shorter. I am going to work on this tonight, I will post the final function.

Thanks for your input.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-29 : 20:52:01

This is a bit shorter than what Rob posted. I only tested it on a couple of time values, @d being the value.

SELECT REPLACE(CONVERT(varchar(20), @d, 108), ':', '')

Tara Kizer
aka tduggan

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-11-29 : 21:00:35

Thanks for the input.

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-11-29 : 21:06:13

Here is what I ended up with.

CREATE FUNCTION UDFMACTIME

(
@Time int
)

RETURNS DATETIME

AS

BEGIN

DECLARE @Varchar_Time VARCHAR(55)
DECLARE @OUTPUT DATETIME

SET @Varchar_Time = CAST(@Time AS VARCHAR(8))
-----------------------------------
IF LEN(@Varchar_Time) = 5

BEGIN
SET @OUTPUT = CAST(SUBSTRING(@Varchar_Time,1,1) + ':' + SUBSTRING(@Varchar_Time,2,2) + ':'
+ SUBSTRING(@Varchar_Time,4,2) AS DATETIME)
END
-----------------------------------

IF LEN(@Varchar_Time) = 6
BEGIN
SET @OUTPUT = CAST(SUBSTRING(@Varchar_Time,1,2) + ':' + SUBSTRING(@Varchar_Time,3,2) + ':'
+ SUBSTRING(@Varchar_Time,4,2) AS DATETIME)
END
-----------------------------------
RETURN @OUTPUT

END

Thanks for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-29 : 21:43:30

alternative solution. You don't have to check for length
@d is your integer time

select  dateadd(hour, @d / 10000,dateadd(minute, @d % 10000 / 100, dateadd(second, @d % 100, 0)))

-----------------
[KH]

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-11-29 : 22:06:47

That is what I was looking for. Thank you very much. Wouldn't have got that one.

Thanks,

TSQLMan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources