CODEWITHSUNDEEP
c#sql-server-2012
Danger Zone
Danger Zone

Reputation: 59

How can I search in month and year in SQL Server 2012?

I'm trying to write a query for searching for month and year only. What I made is searching for month. Can someone tell how I can modify this query to search for month and year like : 5/2019 or 5-2019 ?

select firstname, lastname, date  
from tblInfo 
where data = 02/2019

Upvotes: 0

Views: 65

Answers (3)

i486
i486

Reputation: 6563

Compare DATEPART of date with your constant/variable like:

... WHERE DATEPART(yy,tbleInfoAwareness.date) = 2019 AND DATEPART(mm,tbleInfoAwareness.date) = 5

Another method is to compare date range - it is better because may use index:

.... WHERE tbleInfoAwareness.date BETWEEN '2019-05-01` AND '2019-05-31 23:59:59'

PS: Added time for last day.

Upvotes: 0

Paulo Campez
Paulo Campez

Reputation: 702

You can use the MONTH and YEAR functions from T-SQL:

SELECT firstname, lastname , date FROM tblInfo 
   WHERE MONTH(date) = 2 AND YEAR(date) = 2019

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

Search for the interval between the first day of the month including and the first day of the following month excluding.

SELECT firstname,
       lastname,
       date  
       FROM tblInfo 
       WHERE data >= '2019-02-01'
             AND data < '2019-03-01';

This has the advantage over solutions with datepart() or year() and month() that it can use an index on data if one is there and therefore perform (way) better in that case.

Using a left open interval instead of a closed one (using BETWEEN with the first and last day of the month) also has the advantage that it will also work if data has a time portion. If such a date/time is after the last day of the month at 00:00 but before the first day of the next month (at 00:00), for an arbitrary precision of the time fractions, it will be included.

Upvotes: 1

Related Questions