query - SQL Server Forums

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)

query

Author

Topic

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2010-07-07 : 07:20:52

Hi Folks,

I have some question

I need to convert datetime to date hour

like 2010-07-07 15:39:23.967 into 2010-07-07 15:00:00.000.
Is there any way in Sql server to convert?

Regards
Js.Reddy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-07 : 07:26:17

[code]DECLARE @Sample DATETIME

SET @Sample = '2010-07-07 15:39:23.967'

-- Version 1
SELECT @Sample = CONVERT(CHAR(13), @Sample, 121) + ':00'
SELECT @Sample AS [Version 1]

-- Version 2 (preferred)
SELECT @Sample = DATEADD(HOUR, DATEDIFF(HOUR, 0, @Sample), 0)
SELECT @Sample AS [Preferred][/code]

N 56°04'39.26"
E 12°55'05.63"

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2010-07-07 : 07:33:22

Thanks Peso

quote:

Originally posted by Peso

DECLARE	@Sample DATETIME

SET	@Sample = '2010-07-07 15:39:23.967'

-- Version 1
SELECT	@Sample = CONVERT(CHAR(13), @Sample, 121) + ':00'
SELECT	@Sample AS [Version 1]

-- Version 2 (preferred)
SELECT	@Sample = DATEADD(HOUR, DATEDIFF(HOUR, 0, @Sample), 0)
SELECT	@Sample AS [Preferred]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources