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

Monitoring service broker

Hi!
We have next problem in production.
We have one target that accepts messages from many initiators. Some time target can not get message from some initiator. It may be due hardware problem, for example technitian switch off router. In general service broker succeed to receive all messages when hardware problem was solved (if it takes less than retantion time on initiator side). I would like to build some job that would look at queue of target and could say (or trigger) that from the initiator A for some period of time target did not get any message or did not succeed to close conversation or it constantly have some connectivity problem (like duplicated message).

Any idea?

There are various traces you can use that indicate potential problems, like Message Drop, Message Classify with the subclass Delayed or Connection with the subclass Closing (unless is a ordinary timeout close due to lack of activity). Using the sp_trace_... procedure(s) some automation can be built around them.

An easier solution is to monitor the sys.transmission_queue on all parties involved. If messages are building up, is an indication of a problem that needs to be investigated.