truncate datetime - 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 2000 Forums

Transact-SQL (2000)

truncate datetime

Author

Topic

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-09-19 : 11:23:30

is there any way to truncate this

dateadd(ww,0,getdate() + (1 - DATEPART(WEEKDAY, getdate())))

to 00:00:00.000 instead of the current time?

Kristen
Test

22859 Posts

Posted - 2007-09-19 : 11:31:16

DATEADD(Day, 0, DATEDIFF(Day, 0, YourDateHere))

will "round" the date to midnight preceding - which is equivalent to replace the Time with 00:00:00

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-20 : 04:16:39

or

DATEADD(Day, DATEDIFF(Day, 0, YourDateHere),0)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-20 : 20:06:58

Thanks Madhi, that's what I meant - I don't think there is any "or" about, I don't think what I typed will work properly / safely

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 03:25:49

I think both must work well

SELECT DATEADD(Day, 10,0),DATEADD(Day, 0,10),DATEADD(Day, -10,0),DATEADD(Day, 0,-10)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 04:16:10

Oh well, now I try them and they seem to be equivalent.

But I'm sure I've had a problem in the past using DATEADD(Type, 0, DATEDIFF(...)

Yeah, here you go:


SELECT	DATEADD(Month, DATEDIFF(Month, 0, GetDate()), 0),
	DATEADD(Month, 0, DATEDIFF(Month, 0, GetDate()))

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 04:37:11

Well. I see now

SELECT DATEADD(day, 0,10),DATEADD(month, 0,10),DATEADD(year, 0,10)

In all case 10 is casted to date before adding 0 day/month/year

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 05:47:47

Ah! that makes sense. I never thought about why it was wrong, just slapped my forehead

and fixed it!