0

I am working on an SQL Query where I need to retrieve all the rows from the SQL table where a DateTime field as the Month and Year supplied no matter what the day and time is

My SQL QUERY is as follows

SELECT * From Table where DateCreated=@selectedDate

Table

Id Product Quantity Price DateCreated
1  abc     20        10   2014-12-04 12:26:44.457
2  xyz      25       15   2014-12-14 12:26:44.457

from the above table it should return all the rows with month 12 and year 2014

DoIt
  • 3,270
  • 9
  • 51
  • 103

2 Answers2

2

Try this:

  SELECT * 
  FROM aTable
  WHERE MONTH(DateCreated) = MONTH(@selectedDate) AND
        YEAR(DateCreated) = YEAR(@selectedDate)

Sometimes the answer is just simple.

If you really want to get fancy you could use the technique mentioned here Floor a date in SQL server like so:

  SELECT * 
  FROM aTable
  WHERE 
      dateadd(month,datediff(month,0,DateCreated),0) = 
      dateadd(month,datediff(month,0,@selectedDate),0);
Community
  • 1
  • 1
Hogan
  • 69,564
  • 10
  • 76
  • 117
  • Be aware this method will force a table scan regardless of indexes...if it's a large table you'd be better off making a range to compare against. – JNK Dec 04 '14 at 19:01
  • @JNK - Is that true -- I know on DB2 the YEAR function will do a seek on an indexed date column... does SQL Server act different? – Hogan Dec 04 '14 at 19:05
  • I am pretty certain in the SQL Server `MONTH` is non-sargable. `YEAR` MIGHT be but I doubt it. – JNK Dec 04 '14 at 19:17
0

This will use an index on DateCreated

SELECT *
FROM aTable
WHERE DateCreated >= DATEFROMPARTS(YEAR(@selectedDate),MONTH(@selectedDate),1)
  AND DateCreated <  DATEADD(month,1,DATEFROMPARTS(YEAR(@selectedDate),MONTH(@selectedDate),1))
Anon
  • 10,660
  • 1
  • 29
  • 31