Case statement with date - 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

Case statement with date

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-11-04 : 05:20:30

I have the below variable within a case statement.

DECLARE @test DATETIME
set @test = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SELECT @test = ( CASE WHEN DATEPART(dw, getdate()) =7
THEN (GETDATE()-28)
WHEN DATEPART(dw, getdate()) =1 THEN (GETDATE()-29) else
(GETDATE()-30) END )

select @test

and my result is 2010-10-05 09:17:52.537

but what i am trying to achieve is 2010-10-05 00:00:00.000

can someone point me in the right direction please

MCTS / MCITP certified

Sachin.Nand

2937 Posts

Posted - 2010-11-04 : 05:32:07

[code]
DECLARE @test DATETIME

SELECT @test = ( CASE WHEN DATEPART(dw, getdate()) =7
THEN (GETDATE()-28)
WHEN DATEPART(dw, getdate()) =1 THEN (GETDATE()-29) else
(GETDATE()-30) END )

select DATEADD(DAY, DATEDIFF(DAY, 0, @test), 0)
[/code]

PBUH

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-11-04 : 05:42:47

THank you so much. Always something so small.

Sachin.Nand

2937 Posts

Posted - 2010-11-04 : 06:38:14

quote:
Originally posted by masterdineen

THank you so much. Always something so small.

Yup.I agree.

PBUH

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-11-04 : 09:28:33

To find out the weekday is as follows. CASE WHEN DATEPART(dw, getdate()) =7 is Saturday
how do i find out the month number. ie dw = dayweek. what is month?

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-11-04 : 09:39:58

Its ok i got it. Thank you.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-04 : 09:48:02