none
sql query for month and year of date

    Question

  • I have a table with smallint columns for day,month,year. Now I want to select data lets say for month 10,year 2009 to month 2,year 2010. Can anyone help me in writing this query?
    Wednesday, October 20, 2010 3:36 PM

Answers

  • What about this one? You are passing year and month number and query check it for you as date value.

    use tempDB 
    Declare @BeginYear As SmallInt
    Declare @EndYear As SmallInt 
    Declare @BeginMonth As TinyInt 
    Declare @EndMonth As TinyInt
    
    Set @BeginYear = 2009
    Set @EndYear = 2010 
    Set @BeginMonth = 10
    Set @EndMonth = 2
     
    Declare @BeginDate As Date 
    Declare @EndDate As Date 
    Set @BeginDate = DATEADD(MONTH, 12 * @BeginYear - 22801 + @BeginMonth , 1 - 1)
    Set @EndDate = DATEADD(MONTH, 12 * @EndYear - 22801 + @EndMonth, 31 - 1)
    
    Declare @myTable Table(myDay TinyInt, myMonth TinyInt, myYear SmallInt )
    Insert Into @myTable 
    Select 28, 2, 2010 Union All 
    Select 12, 12, 2009 Union All 
    Select 31, 1, 2010 Union All
    Select 1, 3, 2010
    
    
    Select DATEADD(MONTH, 12 * [myYear] - 22801 + [myMonth], [myDay] - 1) From @myTable Where DATEADD(MONTH, 12 * [myYear] - 22801 + [myMonth], [myDay] - 1) Between @BeginDate And @EndDate 
    

    And Results:

     

    (4 row(s) affected)

     

    -----------------------

    2010-02-28 00:00:00.000

    2009-12-12 00:00:00.000

    2010-01-31 00:00:00.000

     

    (3 row(s) affected)

     


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by schauhan Wednesday, October 20, 2010 5:17 PM
    Wednesday, October 20, 2010 4:13 PM
  • Sorry, I mistyped. It should have been the other way around:

    select * from @myTable where myYear * 12 + myMonth between @BeginYear* 12 + @BeginMonth 
    and @EndYear *12 + @EndMonth 
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by schauhan Wednesday, October 20, 2010 5:28 PM
    Wednesday, October 20, 2010 5:18 PM
    Moderator
  • Rewrite this table and do it correctly with a single DATE data type column. Sure we can do a bunch of ANDs and other tricks, but they stink.  Fix the REAL problem and do not use kludges.  Then fire the old COBOL programmer who thinks of dates as being hard-coded into fields; this is a symptom of many other problems in the DDL. Someone has confused columns with fields and rows with records. 



    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    Wednesday, October 20, 2010 5:42 PM

All replies

  • use tempDB 
    Declare @BeginDate As Date
    Declare @EndDate As Date 
    Set @BeginDate = '10/01/2009'
    Set @EndDate = '02/28/2010'
    
    Declare @myTable Table(myDay TinyInt, myMonth TinyInt, myYear SmallInt )
    Insert Into @myTable 
    Select 1, 2, 2010 Union All 
    Select 12, 12, 2009 Union All 
    Select 1, 3, 2010
    
    
    Select DATEADD(MONTH, 12 * [myYear] - 22801 + [myMonth], [myDay] - 1) From @myTable Where DATEADD(MONTH, 12 * [myYear] - 22801 + [myMonth], [myDay] - 1) Between @BeginDate And @EndDate 
    

    And Results:
    (3 row(s) affected)
    
    -----------------------
    2010-02-01 00:00:00.000
    2009-12-12 00:00:00.000
    
    (2 row(s) affected)
    


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    Wednesday, October 20, 2010 3:39 PM
  • DECLARE @TBL TABLE([day] INT, [month] INT, [year] INT)
    INSERT INTO @TBL( day, month, year ) VALUES (2,1,2009)
    INSERT INTO @TBL( day, month, year ) VALUES (2,2,2009)
    INSERT INTO @TBL( day, month, year ) VALUES (2,3,2009)
    INSERT INTO @TBL( day, month, year ) VALUES (2,4,2009)
    INSERT INTO @TBL( day, month, year ) VALUES (2,2,2010)
    INSERT INTO @TBL( day, month, year ) VALUES (2,4,2010)
    INSERT INTO @TBL( day, month, year ) VALUES (2,5,2010)
    INSERT INTO @TBL( day, month, year ) VALUES (2,10,2010)
    INSERT INTO @TBL( day, month, year ) VALUES (2,11,2010)
    
    SELECT * FROM @TBL
    WHERE [month] BETWEEN 2 AND 10
    AND [year] IN (2009, 2010)
    
    

    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Wednesday, October 20, 2010 3:43 PM
  • the problem with this query is that it also returns records for 2,2009 i dont want that. I just want all records that are from Oct,2009 to Feb,2010.

    Thanks

    Wednesday, October 20, 2010 3:49 PM
  • Did you try my query????
    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    Wednesday, October 20, 2010 3:50 PM
  • the @begindate would me just month and year only. I do not have dates you can say that i want from 1st of any month to last day of any month for specified month and year,like i need all the records from Oct,2009 to Feb,2010
    Wednesday, October 20, 2010 3:51 PM
  • no because this is not what i want Arbi
    Wednesday, October 20, 2010 3:52 PM
  • What about this one? You are passing year and month number and query check it for you as date value.

    use tempDB 
    Declare @BeginYear As SmallInt
    Declare @EndYear As SmallInt 
    Declare @BeginMonth As TinyInt 
    Declare @EndMonth As TinyInt
    
    Set @BeginYear = 2009
    Set @EndYear = 2010 
    Set @BeginMonth = 10
    Set @EndMonth = 2
     
    Declare @BeginDate As Date 
    Declare @EndDate As Date 
    Set @BeginDate = DATEADD(MONTH, 12 * @BeginYear - 22801 + @BeginMonth , 1 - 1)
    Set @EndDate = DATEADD(MONTH, 12 * @EndYear - 22801 + @EndMonth, 31 - 1)
    
    Declare @myTable Table(myDay TinyInt, myMonth TinyInt, myYear SmallInt )
    Insert Into @myTable 
    Select 28, 2, 2010 Union All 
    Select 12, 12, 2009 Union All 
    Select 31, 1, 2010 Union All
    Select 1, 3, 2010
    
    
    Select DATEADD(MONTH, 12 * [myYear] - 22801 + [myMonth], [myDay] - 1) From @myTable Where DATEADD(MONTH, 12 * [myYear] - 22801 + [myMonth], [myDay] - 1) Between @BeginDate And @EndDate 
    

    And Results:

     

    (4 row(s) affected)

     

    -----------------------

    2010-02-28 00:00:00.000

    2009-12-12 00:00:00.000

    2010-01-31 00:00:00.000

     

    (3 row(s) affected)

     


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by schauhan Wednesday, October 20, 2010 5:17 PM
    Wednesday, October 20, 2010 4:13 PM
  • So change it to

    SELECT * FROM @TBL
    WHERE [month] > 2 AND [month]  <=10
    AND [year] IN (2009, 2010)


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Wednesday, October 20, 2010 4:15 PM
  • Naom , i tried this query but it didnt gave me any rows when i know there is data present
    Wednesday, October 20, 2010 5:11 PM
  • Check my new solution. It is based on year and month.
    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    Wednesday, October 20, 2010 5:12 PM
  • Sorry, I mistyped. It should have been the other way around:

    select * from @myTable where myYear * 12 + myMonth between @BeginYear* 12 + @BeginMonth 
    and @EndYear *12 + @EndMonth 
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by schauhan Wednesday, October 20, 2010 5:28 PM
    Wednesday, October 20, 2010 5:18 PM
    Moderator
  • Rewrite this table and do it correctly with a single DATE data type column. Sure we can do a bunch of ANDs and other tricks, but they stink.  Fix the REAL problem and do not use kludges.  Then fire the old COBOL programmer who thinks of dates as being hard-coded into fields; this is a symptom of many other problems in the DDL. Someone has confused columns with fields and rows with records. 



    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    Wednesday, October 20, 2010 5:42 PM