Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.02 seconds. Powered By: Snitz Forums 2000