WHERE Clause to find all records in a specific month

Question

I want to be able to give a stored procedure a Month and Year and have it return everything that happens in that month  how do I do this as I can t compare between as some months have different numbers of days etc   What s the best way to do this  Can I just ask to compare based on the year and month   Thanks

User · Answer

Can I just ask to compare based on the year and month?

You can. Here's a simple example using the AdventureWorks sample database...

DECLARE @Year       INT
DECLARE @Month      INT

SET @Year = 2002
SET @Month = 6

SELECT 
    [pch].* 
FROM 
    [Production].[ProductCostHistory]   pch
WHERE
    YEAR([pch].[ModifiedDate]) = @Year
AND MONTH([pch].[ModifiedDate]) = @Month

User · Answer

Something like this should do it   select   from yourtable where datepart month field     month and datepart year field     year   Alternatively you could split month and year out into their own columns when creating the record and then select against them   Iain

User · Answer

SELECT   FROM yourtable WHERE yourtimestampfield LIKE  AAAA-MM      Where AAAA is the year you want and MM is the month you want

User · Answer

I find it easier to pass a value as a temporal data type  e g  DATETIME  then use temporal functionality  specifically DATEADD and DATEPART  to find the start and end dates for the period  in this case the month e g  this finds the start date and end date pair for the current month  just substitute CURRENT TIMESTAMP for you parameter of of type DATETIME  note the 1990-01-01 value is entirely arbitrary    SELECT DATEADD M             DATEDIFF M   1990-01-01T00 00 00 000   CURRENT TIMESTAMP               1990-01-01T00 00 00 000            DATEADD M             DATEDIFF M   1990-01-01T00 00 00 000   CURRENT TIMESTAMP               1990-01-31T23 59 59 997

User · Answer

One way would be to create a variable that represents the first of the month  ie 5 1 2009   either pass it into the proc or build it  concatenate month 1 year    Then use the DateDiff function   WHERE DateDiff m  Date DateField    0   This will return anything with a matching month and year

User · Answer

More one tip very simple  You also could use to char function  look   For Month  to char happened at    MM     01 For Year  to char happened at    YYYY     2009 For Day   to char happened at    DD     01  to char funcion is suported by sql language and not by one specific database   I hope help anybody more     Abs

User · Answer

If you re using SQL Server  look into DATEPART   http   msdn microsoft com en-us library ms174420 SQL 90  aspx  DATEPART mm   THE DATE YOU RE LOOKING AT    You can then use normal integer logic with it   Same for year  just use yy instead of mm

User · Answer

I think the function you re looking for is MONTH date   You ll probably want to use    YEAR  too   Let s assume you have a table named things that looks something like this   id happend at -- ---------------- 1  2009-01-01 12 08 2  2009-02-01 12 00 3  2009-01-12 09 40 4  2009-01-29 17 55   And let s say you want to execute to find all the records that have a happened at during the month 2009 01  January 2009   The SQL query would be   SELECT id FROM things     WHERE MONTH happened at    1 AND YEAR happened at    2009   Which would return   id --- 1 3 4

User · Answer

Using the MONTH and YEAR functions as suggested in most of the responses has the disadvantage that SQL Server will not be able to use any index there may be on your date column   This can kill performance on a large table   I would be inclined to pass a DATETIME value  e g   StartDate  to the stored procedure which represents the first day of the month you are interested in   You can then use  SELECT     FROM     WHERE DateColumn  gt    StartDate  AND DateColumn  lt  DATEADD month  1   StartDate    If you must pass the month and year as separate parameters to the stored procedure  you can generate a DATETIME representing the first day of the month using CAST and CONVERT then proceed as above   If you do this I would recommend writing a function that generates a DATETIME from integer year  month  day values  e g  the following from a SQL Server blog   create function Date  Year int   Month int   Day int  returns datetime as     begin     return dateadd month    Year-1900  12   Month-1  Day-1      end go   The query then becomes   SELECT     FROM     WHERE DateColumn  gt   Date  Year  Month 1  AND DateColumn  lt  DATEADD month  1  Date  Year  Month 1

User · Answer

As an alternative to the MONTH and YEAR functions  a regular WHERE clause will work too   select   from yourtable where  2009-01-01   lt   datecolumn and datecolumn  lt   2009-02-01

[sql] WHERE Clause to find all records in a specific month

Examples related to sql

Examples related to sql-server-2008