Time Only Function: F_TIME_FROM_DATETIME

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

General SQL Server Forums

Script Library

Time Only Function: F_TIME_FROM_DATETIME

Author

Topic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-29 : 01:58:36

This function converts a SQL Server datetime value to a time only datetime value with the time as an offset from 1900-01-01 00:00:00.000.

By convention, time only is usually stored this way in SQL Server. The reason for this is that the time can be added to a datetime value containing the date only, and produce the original date and time. Example:


select
	COMBINED_DATE =
	convert(datetime,'2006-04-27 00:00:00.00') +
	convert(datetime,'1900-01-01 07:23:11.247')

Results:

COMBINED_DATE
------------------------ 
2006-04-27 07:23:11.247

(1 row(s) affected)

This link has information about date and time in SQL Server and links to other date and time scripts in the Script Library forum:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

This code contains the function and a script to demo it:



if objectproperty(object_id('dbo.F_TIME_FROM_DATETIME'),'IsScalarFunction') = 1
	begin drop function dbo.F_TIME_FROM_DATETIME end
go
create function dbo.F_TIME_FROM_DATETIME
	( @DAY datetime )
returns  datetime
as
/*
Function: F_TIME_FROM_DATETIME
	Finds time only from a datetime, and returns
	the time as an offset from 1900-01-01 00:00:00.000

	@DAY                    F_TIME_FROM_DATETIME 
	----------------------- -----------------------
	2006-04-27 07:23:11.247 1900-01-01 07:23:11.247
	1753-01-01 00:00:00.003 1900-01-01 00:00:00.003
	9999-12-31 23:59:59.997 1900-01-01 23:59:59.997

	Valid for all SQL Server datetimes.
*/
begin

return  @DAY-dateadd(dd,datediff(dd,0,@DAY),0)

end
go

--Demo dbo.F_TIME_FROM_DATETIME

select
	[@DAY] = DT,
	F_TIME_FROM_DATETIME = dbo.F_TIME_FROM_DATETIME( DT )
from
	(
	select DT = convert(datetime,'2006-04-27 07:23:11.247')
	union all
	select DT = convert(datetime,'2006-04-27 07:21:09.333')
	union all
	select DT = convert(datetime,'1753-01-01 00:00:00.003')
	union all
	select DT = convert(datetime,'1753-01-01 07:21:09.997')
	union all
	select DT = convert(datetime,'1753-01-01 23:59:59.997')
	union all
	select DT = convert(datetime,'9999-12-31 00:00:00.003')
	union all
	select DT = convert(datetime,'9999-12-31 07:21:09.997')
	union all
	select DT = convert(datetime,'9999-12-31 23:59:59.997')
	) a


Results:

@DAY                    F_TIME_FROM_DATETIME
----------------------- -----------------------
2006-04-27 07:23:11.247 1900-01-01 07:23:11.247
2006-04-27 07:21:09.333 1900-01-01 07:21:09.333
1753-01-01 00:00:00.003 1900-01-01 00:00:00.003
1753-01-01 07:21:09.997 1900-01-01 07:21:09.997
1753-01-01 23:59:59.997 1900-01-01 23:59:59.997
9999-12-31 00:00:00.003 1900-01-01 00:00:00.003
9999-12-31 07:21:09.997 1900-01-01 07:21:09.997
9999-12-31 23:59:59.997 1900-01-01 23:59:59.997

(8 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources