Calculate Last and First Day of the Month
-
I'm working on a stored procedure that's selecting records based on whether their effective and termination dates are within a given time period. The time period would be the begining and the end of the month. For example, if the date past into the proc was 02/15/2004, the time period we would be going against would be 02/01/2004 -- 02/28/2004.
The select statement, if hard coded, would look like this if the date being past into it was 02/15/2004:
SELECT * FROM Members
WHERE effective_dt >='02/01/2004' AND
termination_dt <='02/28/2004'
-
How about something like the following (note that it is easier to use 'less than the first of the next month' instead of trying to figure that last day of the current month and using 'less than or equal to'):
SELECT *
FROM Members
WHERE effective_dt >= CONVERT(datetime, CONVERT(varchar(2), DatePart(mm, @date)) + '/01/' + CONVERT(varchar(4), DatePart(yy,@date)))
AND termination_dt < DateAdd(mm,1,CONVERT(datetime, CONVERT(varchar(2), DatePart(mm, @date)) + '/01/' + CONVERT(varchar(4), DatePart(yy,@date))))
Mike
-
CREATE PROC dbo.InMonth @Date datetime AS
SET NOCOUNT ON
SELECT Member_ID, Effective_Dt, Termination_Dt
FROM Members
WHERE Effective_Dt >= DATEADD(d,1-DAY(@Date),CONVERT(char(8),@Date,112))
AND Termination_Dt < DATEADD(m,1,DATEADD(d,1-DAY(@Date),CONVERT(char(8),@Date,112)))
--Jonathan -
CREATE PROC dbo.InMonth @Date datetime AS
SET NOCOUNT ON
SELECT Member_ID, Effective_Dt, Termination_Dt
FROM Members
WHERE
DatePart(m, Effective_Dt) = DatePart(m, @Date) and
DatePart(yyyy, Effective_Dt) = Datepart(yyyy, @Date) and
DatePart(m, Termination_Dt) = DatePart(m, @Date) and
DatePart(yyyy, Termination_Dt) = DatePart(yyyy, @Date)
-- Carlos Szittyay
-
Ain't date handling marvellous? All sorts of ways of "skinning the cat". My approach to getting the first day of the month and first of the next is as follows ... (I always use alpha for the month ... saves confusion).
declare @today datetime
select @today=getdate()
- select
- '01 '+substring(convert(char(11),@today,106),4,11),
'01 '+substring(convert(char(11),dateadd(month,1,@today),106),4,11)
01 Feb 2004 01 Mar 2004
-
All,
Thanks a lot for the suggestions...
Fred
-
With regard to the start and end dates of the month, don't forget that a query like this...
SELECT * FROM Members
WHERE effective_dt >='02/01/2004' AND
termination_dt <='02/28/2004'
...will leave out all the members whose termination date was 02/28/2004. When you do a comparison against 02/28/2004, you're talking about the first millisecond at the start of the day -- not the whole day thru to "02/28/2004 11:59.59.997 PM"
Better would be either...
SELECT * FROM Members
WHERE effective_dt >= '02/01/2004' AND
termination_dt <= "02/28/2004 11:59.59.997 PM" --note the use of less-than...
...or...
SELECT * FROM Members
WHERE effective_dt >= '02/01/2004' AND
termination_dt < '03/01/2004' --note the use of less-than...
To simplify this for myself, I wrote a UDF like so...
CREATE FUNCTION fn_LastMomentOfDay (
@dtInput as datetime
 
/*-------------------------------------------------------------
Takes the submitted date and returns the very last moment
of that same date. So if @dtInput = '2003-05-21 12:27:32'
then @dtOutput = '2003-05-21 23:59:59.997'
Why not ".999" seconds? The datetime data type doesn't
allow that degree of precision. This is the best we can do.
-------------------------------------------------------------*/
RETURNS datetime
AS
BEGIN
DECLARE @dtOutput as datetime
SET @dtOutput = @dtInput
SET @dtOutput = CAST( FLOOR( CAST( @dtOutput as float ) ) AS datetime )
SET @dtOutput = dateadd( ms, -3, dateadd(dd, 1, @dtOutput ) )
RETURN @dtOutput
END
-
declare @year int
declare @period int
select @year = 2004,@period = 2
select * from MEMBERS where datepart(year,effective_dte) = @year
and datepart(month,effective_dte) = @period
That way, in a proc, all you have to pass is year and period
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply