date problem

Can someone tell me what is wrong with this statement:

case when DatePart('day',getDate())='Monday' then DateAdd('day',-3,getDate()) else DateAdd('day',-1,getDate())

The date part function will return an INT for the day passed in not Mon, Tues, Wed..ect.

SELECTDATEPART(DAY,GETDATE()) = 'Monday' will be invalid because you are comparing an int to a string and it will fail conversion.

Example SELECT DATEPART(DAY,'6/22/2007') will return 22.

|||

use the following expression,

CaseWhendatename(w,getdate())='Monday'ThenDateAdd('day',-3,getDate())elseDateAdd('day',-1,getDate())

|||

or this...

this will work

Code Snippet

select

casewhenDatePart(dw,getDate())= 1

thenDateAdd(d,-3,getDate())

elseDateAdd(d,-1,getDate())end

|||

or this...

this will work

... whenDatePart(dw,getDate())= 1

As long as DATEFIRST has been set to Monday instead of the US default setting of Sunday (which makes Monday = 2) ...

And of course, it may work just fine and then someday someone changes the DATEFIRST setting.

|||Execellent choice Mani -your suggestion isn't dependent upon the DATEFIRST setting...sql