Converting my Date time 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 my Date time to Seconds

Author

Topic

Abhishek_1987
Starting Member

3 Posts

Posted - 2012-05-28 : 03:35:25

I want to convert Date time or nvarchar of format hh:mm:ss to seconds. Where my date1 and date2 are not pre-defined. I have a table with two columns Start date and End Date. I want the difference of this time in seconds. I have nearly 15k records in my table. How can i acheieve that?

abhi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-28 : 03:54:17

[code]
select datediff(second, start_date, end_date)
from [15k_table]
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 14:03:43

if you want it in hh:mm:ss format you need to modify it like


select convert(varchar(8),dateadd(second,datediff(second, start_date, end_date),0),108)
from   [15k_table]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-05-29 : 09:29:05

If the difference is greater than a certain amount (a little over 68 years), you will get an overflow on DATEDIFF(DD,StartDate,EndDate) and will need a more complex seconds difference calculation:

print 'Diff 1'
go
select DD1=datediff(ss,'19500101','20180119 03:14:07')
go
print 'Diff 2'
go
select DD2=datediff(ss,'19500101','20180119 03:14:08')
go
print 'Diff in Seconds'
select
	a.*,
	SecondsDiff = 
	-- Days difference time 86400 (number of seconds in a day)
	(datediff(dd,a.DT1,a.DT2)*000000000086400)+
	-- Seconds since start of day
	datediff(ss,dateadd(dd,datediff(dd,a.DT1,a.DT2),a.DT1),a.DT2)
from
	( -- Test Data
	select DT1 = convert(datetime,'19000101'), DT2 = getdate()	union all
	select DT1 = '19500101',	DT2 = '20180119 03:14:07'	union all
	select DT1 = '19500101',	DT2 = '20180119 03:14:08'	union all
	select DT1 = '17530101',	DT2 = '99990101 23:59:59'
	) a

Results:

Diff 1
DD1
-----------
2147483647

(1 row(s) affected)

Diff 2
Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Diff in Seconds
DT1                     DT2                     SecondsDiff
----------------------- ----------------------- ---------------------------------------
1900-01-01 00:00:00.000 2012-05-29 09:22:07.713 3547272127
1950-01-01 00:00:00.000 2018-01-19 03:14:07.000 2147483647
1950-01-01 00:00:00.000 2018-01-19 03:14:08.000 2147483648
1753-01-01 00:00:00.000 9999-01-01 23:59:59.000 260218655999

(4 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources