Converting from hours/minutes to seconds

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 from hours/minutes to seconds

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2005-05-16 : 13:03:09

Hi,
I have a varchar field that contains values like 00:17:17, 01:04:47
which symbolize the time.

How can I convert this to seconds?
00:17:17 --> 1037 seconds
01:04:47 --> 3887 seconds

Thanks,
Ninel

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-16 : 13:18:38

I get 1020


DECLARE @x varchar(25), @y datetime
SELECT @x = CONVERT(datetime,'00:17:17'), @y = '1900-01-01'
SELECT @x, @y
SELECT DATEDIFF(ss,@y,@x)

Brett

8-)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-05-16 : 13:25:34

quote:
I get 1020

Ugh. That's because you got the default datetime to varchar conversion, which doesn't include seconds.


DECLARE @x varchar(8)
SELECT @x = '00:17:17'
SELECT DATEDIFF(ss, 0, @x)

1037.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-16 : 13:55:46

advice: use the proper datatype to store your values, or simply store it in seconds, do not use varchars for this.

- Jeff

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-05-16 : 14:20:54

This seems to work fine:


select
	S1 = datediff(ss,0,'00:17:17'), 
	S2 = datediff(ss,0,'01:04:47')

S1          S2          
----------- ----------- 
1037        3887

(1 row(s) affected)

CODO ERGO SUM

ninel
Posting Yak Master

141 Posts

Posted - 2005-05-16 : 14:26:19

Here's another questions sort of along the same lines...

How do I add them up the varchar field values and maintain the same format?

00:7:17 + 01:04:47 = 1:12:04

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-16 : 14:38:39

You change them from a varchar field to a datetime field.

*need more coffee*

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-16 : 14:44:07

quote:
How do I add them up the varchar field values and maintain the same format?

Like Jeff told you, start with making then the correct format in the DB. Its easier to start correct than to fix it after.

Jim
Users <> Logic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-05-16 : 14:44:58

This will do if is is less than 24 hours, but what format do you use when you have more than 24 hours?




select
	[time] =
	left(convert(varchar(30),
	dateadd(ss,datediff(ss,0,'00:17:17')+datediff(ss,0,'23:59:47'),0)
	,114),8)

time     
-------- 
00:17:04

quote:
Originally posted by ninel

Here's another questions sort of along the same lines...

How do I add them up the varchar field values and maintain the same format?

00:7:17 + 01:04:47 = 1:12:04

CODO ERGO SUM

ninel
Posting Yak Master

141 Posts

Posted - 2005-05-16 : 14:52:18

I am making these calculations from processes that already exist and i cannot change them. These fields symbolize login and logout times that I must add up. Employees log in and log out daily. They should not add up over 24 hours.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-16 : 14:56:23

quote:
Originally posted by ninel

These fields symbolize login and logout times that I must add up. Employees log in and log out daily. They should not add up over 24 hours.

All the more reason to fix it at the DB (the source).

Jim
Users <> Logic

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-16 : 14:57:01

[code]

Declare @seconds int
Set @seconds = 654321

Select 1, convert(varchar,@seconds/(3600)) + 'hrs ' + convert(varchar,(@seconds%3600)/60) + 'minutes ' + convert(varchar,(@seconds%3600)%60) + ' seconds' Union
Select 2, convert(varchar,@seconds/(3600)) + ':' + convert(varchar,(@seconds%3600)/60) + ':' + convert(varchar,(@seconds%3600)%60) Union
Select 3, convert(varchar,@seconds/(24*60*60)) + 'days ' + convert(varchar,@seconds/(60*60)%24) + 'hrs ' + convert(varchar,(@seconds%3600)/60) + 'minutes ' + convert(varchar,(@seconds%3600)%60) + ' seconds' Union
Select 4, convert(varchar,@seconds/(24*60*60)) + ':' + convert(varchar,@seconds/(60*60)%24) + ':' + convert(varchar,(@seconds%3600)/60) + ':' + convert(varchar,(@seconds%3600)%60)
[/code]

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

ninel
Posting Yak Master

141 Posts

Posted - 2005-05-16 : 15:10:32

Michael,

I don't understand how your solution adds the varchar values?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources