convert a value in time format 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

SQL Server Development (2000)

convert a value in time format to seconds

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-10-09 : 09:29:33

Hi,

I have a time value in format like so 01:04:05

is there a sql function that converts this into seconds so that it becomes 3845

thanks

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-10-09 : 10:13:46

You could write a UDF to do this.


create function ToSeconds(@CurrentDate datetime)
returns int
as
begin
	declare @Sec int
	declare @Min int
	declare @Hour int
	declare @Total int
	set @Sec = datepart(ss, @CurrentDate)
	set @Min = datepart(mi, @CurrentDate) * 60
	set @Hour = datepart(hh, @CurrentDate) * 60 * 60

	set @Total = @Sec + @Min + @Hour
	return @Total
end

hth,
Justin

"I want to thank you. You could've given us help, but you've given us so much more."

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-10-09 : 11:30:05

looks so simple. I'm kinda new to sql

Thanks!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-10-09 : 11:55:18

declare @somedate datetime
select @somedate = '20031009 01:04:05'
select datediff(ss,dateadd(dd,datediff(dd,0,@somedate),0),@somedate)

Jay White
{0}

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-10-09 : 13:20:32

quote:

select datediff(ss,dateadd(dd,datediff(dd,0,@somedate),0),@somedate)

Nice, very succinct.

I pickup something new everyday.

"I want to thank you. You could've given us help, but you've given us so much more."

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-10-13 : 07:21:29

Hi thanks for the input! helped me out a lot. Just to expand on this can you tell me then how to convert a value in seconds back into the original time format i.e. i have a value 3824 (which is seconds) i then want to covert it back into 01:03:44.

Thanks

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-13 : 07:49:03

Simply reverse it!

SELECT dateadd(ss, @seconds, 0)

Owais

Make it idiot proof and someone will make a better idiot

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-10-13 : 09:20:18

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources