CODEWITHSUNDEEP
sql-server
akd
akd

Reputation: 6758

Querying based on date range by using month and year numbers in SQL

DECLARE @startMonth int
DECLARE @endMonth int

DECLARE @startYear int
DECLARE @endYear int

SET @startMonth = 1
SET @endMonth =5

SET @startYear =2014
SET @endYear =2015

SELECT * FROM Table 
         WHERE (YEAR(Date)>=@startYear AND MONTH(Date) >= @startMonth)
              AND (YEAR(Date)<=@endYear AND MONTH(Date) <= @endMonth)

This is apparently returning results of any date between 2014-01-01 to 2014-05-31

and 2014-01-01 to 2015-05-31

but I would like to get any date from 2014-01-01 to 2015-05-31 instead.

How should I change the query? I should write the following?

SELECT * FROM Table 
         WHERE Date>=DATEFROMPARTS ( @startYear, @startMonth, 1 ) 
           AND Date <= DATEFROMPARTS ( @endYear, @endMonth, 31 ))

Then I end up with the problem that if the @endMonth does not contain 31 days. Then I would have to create another checking to ensure the correct number of end date.

I am sure there must be a better way of writing this. I would appreciate your help.

Upvotes: 0

Views: 72

Answers (2)

DVT
DVT

Reputation: 3127

SELECT *
FROM
    <table_name>
WHERE
    date BETWEEN DATEFROMPARTS(@startYear,@startMonth,1) AND EOMONTH(DATEFROMPARTS(@endYear,@endMonth,1));

Upvotes: 0

juergen d
juergen d

Reputation: 204746

SELECT * 
FROM your_table 
WHERE Date >= DATEFROMPARTS(@startYear, @startMonth, 1)
  AND Date <  DATEFROMPARTS(@endYear, @endMonth + 1, 1)

Upvotes: 1

Related Questions