SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Time Only Function: F_TIME_FROM_DATETIME
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/29/2006 :  01:58:36  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 04/29/2006 14:35:06
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000