Getting the 1st of the month, date

Hi all,

I have a SP that is passed a date, and then need to do a test of some data returned from the DB by getting the passed Date, finding out its month, and then using the BETWEEN clause to get all rows from teh DB where their date falls between the 1st and the last (28th, 30th, 31st) of the month contained in the Date that is passed to the SP. I currently have the following SQL in my WHERE clause to get the first day of the month, but it seems long-winded. Is there a smaller, smarter way of getting it...

DATEADD(MONTH, +1, DATEADD(MONTH, -1, @.RequiredMonth))

Thanks

Tryst

DATEADD(month,DATEDIFF(month,0,@.RequiredMonth),0) will give you the first of the month

DATEADD(month,DATEDIFF(month,0,@.RequiredMonth),1) will give you the first of the next month
Then you can do this:

WHERE field1>=DATEADD(month,DATEDIFF(month,0,@.RequiredMonth),0) AND field1<DATEADD(month,DATEDIFF(month,0,@.RequiredMonth),1)

That'll give the best performance, because the dates are only calculated once, and then compared to each field1 value, possibly using indexes to limit the search.

You can also:

WHERE MONTH(field1)=MONTH(@.RequiredMonth) AND YEAR(field1)=YEAR(@.RequiredMonth)

however, execution time will be much higher as the MONTH and YEAR expressions have to be calculated for every row, and may not participate in any index optimizations.

It appears that probably you had something similiar at one time, but someone tried to "optimize" it by using DATEADD with -1, not realizing it is NOT the same as DATEDIFF.

|||ok - thanks for the help Motley.

Will look into them.

Thanks again.

Tryst