Month/Year comparison against a date

Hi,

Is there a way to get the last day of a month, given a date in a datetime variable?

I have a stored procedure that accepts a datetime parameter. I need to find the last day of the month for that parameter value. For example, if the stored procedure is passed the datetime value, '6/13/2007', I need to be able to get from that '6/30/2007.'

Here's the bigger picture: The sp actually takes two datetime parameters (unfortunately, I don't have access to change the user interface). The sp needs to select records between the month/years of those dates, not including the first but including the second. For example, if the user specifies the following dates:

6/7/2006
7/15/2007

the sp needs to select all records that come after 6/30/2006 and on or before 7/31/2007.

I've tried this: ("ReportDate" is the name of the datetime field in a table in the sp and "@.StartDate" and "@.EndDate" are the datetime parameters in the sp)

Month(ReportDate) > Month(@.StartDate) And Year(ReportDate) >= Year(@.StartDate) And
Month(ReportDate) <= Month(@.EndDate) And Year(ReportDate) <= Year(@.EndDate)

But this returns fewer records than when I enter 6/30/2006 and 7/31/2007 as the parameters and just compare dates like this:

ReportDate > @.StartDate And ReportDate <= @.EndDate

Thank you.

Something like this:

Code Snippet

WHERE ( ReportDate >= dateadd( month, datediff( month, 0, @.StartDate ) + 1, 0 )

AND ReportDate < dateadd( month, datediff( month, 0, @.EndDate ) + 1, 0 )
)

You do NOT want to enclose ReportDate in a function since that will most likely result in not using indexing.

|||

WHERE ( ReportDate > dateadd( month, datediff( month, 0, @.StartDate ) , -1 )

AND ReportDate < dateadd( month, datediff( month, 0, @.EndDate ) + 1,0 )
)

I just edited the previous reply to make it more close to your requirement..

|||

I don't think that is exactly 'right'. (I did make the assumption that the OP wanted ONLY JULY 2007 dates.)

Follow this code:

Code Snippet

DECLARE
@.StartDate datetime,
@.EndDate datetime

SELECT
@.StartDate = '06/7/2007',
@.EndDate = '7/15/2007'

-- Arnie's Variation
-- Starting at midnight, 7/1/2007, Ending at midnight, 8/1/2007
SELECT
'Arnie',
dateadd( month, datediff( month, 0, @.StartDate ) + 1, 0 ),
dateadd( month, datediff( month, 0, @.EndDate ) + 1, 0 )

-- Mandip's Variation
-- Starting at midnight, 5/31/2007, ending at midnight, 8/1/2007
SELECT
'Mandip',
dateadd( month, datediff( month, 0, @.StartDate ) , -1 ),
dateadd( month, datediff( month, 0, @.EndDate ) + 1,0 )

-- OP Requested
-- the sp needs to select all records that come
-- after 6/30/2006 and on or before 7/31/2007

-- -
Arnie 2007-07-01 00:00:00.000 2007-08-01 00:00:00.000

Mandip 2007-05-31 00:00:00.000 2007-08-01 00:00:00.000

Month & Year comparison

Hi,

The problem here in hand is comparing the month & year ranges rather than the entire date. I am passing a start month and start year along with the end month and end year, now the query that i have written does not seem to be appropriate

My Query:

MONTH(Date_Added) BETWEEN @.StartMonth AND @.EndMonth

AND YEAR(Date_Added) BETWEEN @.StartYear AND @.EndYear

For some reason this works fine if i specify the startmonth as 1 and endmonth as 12, whereas if i specify 1 and 1 it doesnot work. Can anyone help me by refining my query so that i can search a particular date within mm/yyyy and mm/yyyy.

Thanks in advance.

Santosh

So if you put in between January and March and between 2004 and 2006, and then select for April 2005, it wouldn't qualify, right?

Do you really mean to select that way, or do you want to select between January 2004 and March 2006?

If you want to select continuous dates, set your first date to the first day of the month and your last date to the last day of the month and then search between those two values.

Here's a function to set a date to the first day of the month. You can use the same logic to set a date to the last day of the month.

http://www.sql-server-helper.com/functions/get-first-day-of-month.aspx

|||I would like to select between January 2004 and March 2006?|||

That'swhat I figured you want. You can't unlink the logic. Basically you have to have your year/months combined into a single variable and then do a between for those dates. Set the earlier date to the first of the month and your later date to the end of the month.

|||

WHERE Date_Added >= CAST(CAST(@.StartYear AS VARCHAR(4)) + '/' + CAST(@.StartMonth AS VARCHAR(2)) + '/01' AS DATETIME) AND Date_Added < DATEADD(month,1,CAST(CAST(@.EndYear AS VARCHAR(4)) + '/' + CAST(@.EndMonth AS VARCHAR(2)) + '/01' AS DATETIME))

|||

Yeah, that's the idea.