Converting datetime field to time only

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 datetime field to time only

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2007-12-31 : 11:03:58

Hello All,

Does anyone know how to write an update statement to convert column B into time only? Below are before and after table for illustration:

Before the update statement:
A B C
29 Dec 29 2007 4:00PM 3
29 Dec 29 2007 4:15PM 2
29 Dec 29 2007 4:30PM 3
29 Dec 29 2007 4:45PM 0
29 Dec 29 2007 5:00PM 0
29 Dec 29 2007 5:15PM 0
29 Dec 29 2007 5:30PM 8
29 Dec 29 2007 5:45PM 0
29 Dec 29 2007 6:00PM 0
29 Dec 29 2007 6:15PM 0
29 Dec 29 2007 6:30PM 0
29 Dec 29 2007 6:45PM 9

After the update statement:
A B C
29 4:00PM 3
29 4:15PM 2
29 4:30PM 3
29 4:45PM 0
29 5:00PM 0
29 5:15PM 0
29 5:30PM 8
29 5:45PM 0
29 6:00PM 0
29 6:15PM 0
29 6:30PM 0
29 6:45PM 9

Please advice. Thanks.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-31 : 11:06:53

See if this helps: http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=38940

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-31 : 11:14:38

what is the data type for Column B ?

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-31 : 11:25:53

I would also recommend this topic from Jeff's blog:
http://weblogs.sqlteam.com/jeffs/archive/2007/04/13/60175.aspx

the general subject is that though you can manipulate a string representation of a datetime value, you cannot change the nature of how datetimes are stored. Via a select statement you can present a datetime value anyway you want (as a formatted string). But you cannot update a datetime value to only store the time alone (or the date alone) or store any specific "date format".

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-12-31 : 11:27:12

This code 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. The function on the link below has this code as a user defined function.

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

Time Only Function: F_TIME_FROM_DATETIME
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65358



select
	a.DT,
	Time_Only = a.DT-dateadd(dd,datediff(dd,0,a.DT),0)
from
	(
	-- Test Dates
	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:
DT                       Time_Only
-----------------------  -----------------------
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