Time Caluclation - 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 Caluclation

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-14 : 07:17:19

Hi,

Is It possible to Calculate the time in sql...?
Like

I have the following

8.30 AM
4:30 PM

I need the Diffrence Between them

any ideas..?

Thanks
KK

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 07:23:51

quote:
Originally posted by CSK

Hi,

Is It possible to Calculate the time in sql...?
Like

I have the following

8.30 AM
4:30 PM

I need the Diffrence Between them

any ideas..?

Thanks
KK

That would be the DATEDIFF function.

See Books Online.

SELECT DATEDIFF( *datetimepart*, earliest datetime, latest datetime)

such as

SELECT DATEDIFF(mi, '8:30 AM', '4:30 PM') --> 480 minutes
SELECT DATEDIFF(hh, '8:30 AM', '4:30 PM') --> 8 hours

Peter Larsson
Helsingborg, Sweden

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-14 : 07:33:25

thanks peter

Thanks
KK

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-14 : 07:46:33

You can also subtract the earlier time from the later time and get a result that represents the difference as an elapsed time since the SQL Server "zero" time point, 1900-01-01 00:00:00.000

With the elapsed time, you can extract days, hours, minutes, seconds, and milliseconds.


select
	Days = datediff(day,0,ET),
	Hours = datepart(hour,ET),
	Minutes =  datepart(minute,ET),
	Seconds =  datepart(second,ET),
	Milliseconds=  datepart(millisecond,ET),
	[Elapsed Time] = ET
from
	-- Get time difference
	(select	ET = convert(datetime,'4:30 PM')-convert(datetime,'8:30 AM')) a


Results:

Days Hours Minutes Seconds     Milliseconds Elapsed Time
---- ----- ------- ----------- ------------ -----------------------
0    8     0       0           0            1900-01-01 08:00:00.000
(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources