Inserting time in smallint field

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)

Inserting time in smallint field

Author

Topic

pilotom
Starting Member

6 Posts

Posted - 2008-03-14 : 10:17:20

I need to insert rows into a table which contains a smallint field
for time. The times are stored in that colum as integers (898,
11345, 1259, etc.) How can I enter a time like 9:15 AM into this field? I know how to display integer data in hh:mm format but I'm stumped on how I can do the reverse.

Thanks for any help offered.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 10:20:35

This is another example of why you always should use proper datatype!

Maybe it is that simple as multiplying hours by 60 and add number of minutes (09:15 yields 555).

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-14 : 10:25:01

quote:
898, 11345

maybe it is no of seconds since midnight ?

KH
[spoiler]Time is always against us[/spoiler]

pilotom
Starting Member

6 Posts

Posted - 2008-03-14 : 10:43:55

In the client application that displays the time, an integer entered into the database as 858 displays as 02:58 PM. 11345 displays as 08:59 PM.

MP

pilotom
Starting Member

6 Posts

Posted - 2008-03-14 : 10:45:01

CORRECTION - that's 898 displaying as 02:58 PM.

MP

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-14 : 10:51:01

quote:
Originally posted by pilotom

CORRECTION - that's 898 displaying as 02:58 PM.

MP

for 898, looks like it is no of minutes since midnight. But not sure about the 11345

SELECT	tm, [time] = CONVERT(varchar(5), DATEADD(minute, tm, 0), 108)
FROM
(
	SELECT	tm = 898 	UNION ALL
	SELECT	tm = 11345
) t
/*
tm          time  
----------- ----- 
        898 14:58
      11345 21:05
*/

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 11:01:48

quote:
Originally posted by khtan

maybe it is no of seconds since midnight ?

A smallint maximum value is 32768 and there are 86400 second per day, so I don't think so.

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-14 : 11:09:20

quote:
Originally posted by Peso

quote:
Originally posted by khtan

maybe it is no of seconds since midnight ?

A smallint maximum value is 32768 and there are 86400 second per day, so I don't think so.

E 12°55'05.25"
N 56°04'39.16"

Oh, i missed the smallint part.

KH
[spoiler]Time is always against us[/spoiler]

pilotom
Starting Member

6 Posts

Posted - 2008-03-14 : 11:32:00

[/quote]
for 898, looks like it is no of minutes since midnight. But not sure about the 11345

SELECT	tm, [time] = CONVERT(varchar(5), DATEADD(minute, tm, 0), 108)
FROM
(
	SELECT	tm = 898 	UNION ALL
	SELECT	tm = 11345
) t
/*
tm          time  
----------- ----- 
        898 14:58
      11345 21:05
*/

[/quote]

Kthan this is actually perfect for converting the integer to
military time. That 11345 was actually 9:05 PM - sorry about that. On my end I modified it to work like this:

declare @tm int
set @tm=11345
select CONVERT(varchar(5), DATEADD(mi, @tm, '1 jan 1970'), 108)

However, what I need is something similar that would allow me to plug
in 14:05 and get 11345.

MP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 11:38:44

You can't! You are not using the proper datatype, it's that easy.
You have to enter a SMALLINT value.

Do the math beforehand. Multiply hours (24 hour clock) by 60 and add the minutes.
Or using AM/PM, Multiply hours by 60 and add minutes. If on PM, add 720 to the sum.

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-14 : 11:46:27

Wow .. it is amazing the things people do to make simple things complicated, isn't it?

It's like adding oil to your car, but only accessing the engine through the glove compartment.

- Jeff
http://weblogs.sqlteam.com/JeffS

pilotom
Starting Member

6 Posts

Posted - 2008-03-14 : 11:53:45

quote:
Originally posted by Peso
Do the math beforehand. Multiply hours (24 hour clock) by 60 and add the minutes.
Or using AM/PM, Multiply hours by 60 and add minutes. If on PM, add 720 to the sum.

This worked great Peso - thanks. I still don't understand where
the 11345 came from but if you enter 1985 which is the number you get
with your calculations for 9:05pm you get 9:05pm to display in the client and that's what I need. THANKS EVERYONE!

pilotom
Starting Member

6 Posts

Posted - 2008-03-14 : 11:56:09

quote:
Originally posted by jsmith8858

Wow .. it is amazing the things people do to make simple things complicated, isn't it?

It's like adding oil to your car, but only accessing the engine through the glove compartment.

- Jeff
http://weblogs.sqlteam.com/JeffS

I agree Jeff. Sadly - it's not my db - just working with it now.

MP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources