Updating Time Only in 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

SQL Server 2005 Forums

Transact-SQL (2005)

Updating Time Only in datetime

Author

Topic

geo039
Starting Member

8 Posts

Posted - 2010-07-16 : 16:46:31

Does anyone know of an update statement to update timeonly in datetime field in sql? I want to update the default time of 12:00:00 am to 11:59:00 pm and can't find a sample anywhere online

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-07-16 : 17:12:51

select
	MyDate,
	NewMyDate = dateadd(dd,datediff(dd,0,MyDate),'11:59:00.000')
from
	( --Test Data
	Select MyDate = getdate() union all
	select '20100814 00:00:00.000'
	) a

Results:

MyDate                  NewMyDate
----------------------- -----------------------
2010-07-16 17:10:56.443 2010-07-16 11:59:00.000
2010-08-14 00:00:00.000 2010-08-14 11:59:00.000

(2 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources