DateTime Quesiton - 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

General SQL Server Forums

New to SQL Server Programming

DateTime Quesiton

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-18 : 09:52:26

Hi,

i have a datetime variable , lets say;

@date = 2006-07-18 16:51:13

and i wanna put zero to the seconds part of it, like this below;

@date = 2006-07-18 16:51:00

how can i do it?

thanks

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-18 : 09:57:18

declare @date datetime
set @date = '20060718 16:51:13'

select @date as Date, dateadd(second, -datepart(second, @date), @date) as RoundedDownToMinute

/*results
Date                                                   RoundedDownToMinute                                    
------------------------------------------------------ ------------------------------------------------------ 
2006-07-18 16:51:13.000                                2006-07-18 16:51:00.000
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-18 : 10:00:03

@dateZero = dateadd(second, -1*datepart(second, @date), @date)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-18 : 11:33:32

The solutions already posted do not remove the milliseconds part of a datetime.

You can use the F_START_OF_MINUTE function in this link to do what you want:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

It does it this way:

select dateadd(ms,-(datepart(ss,@date)*1000)-datepart(ms,@date),@date)

CODO ERGO SUM

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-19 : 07:33:05

Good point, Michael

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-19 : 07:38:16

Another variation


declare @date datetime
set @date = '20060718 16:51:13.45'

select	dateadd(minute, datepart(minute, @date), dateadd(hour, datediff(hour, 0, @date), 0))

/* RESULT

	2006-07-18 16:51:00.000
*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources