How do I get month and year from date type in sql-CodePudding

I am trying to get data from a specific month and year from a start date and end date with date datatypes. lets say the start date is march 7 2017 and the end date is dec 15 2035

Declare @monthid as varchar(8)
set @monthid = 'jan 2020'

Convert(char(4), startdate,100)   convert(char(4), startdate,120) <= @monthid
Convert(char(4), enddate,100)   convert(char(4), enddate,120) >= @monthid

CodePudding user response：

Instead of string manipulation and then a lot of effort to manipulate the existing data you should use the proper datatype in your variable. This has two benefits. The first being that your code would be a lot simpler. The second being that your code is sargable so you can utilize any indexes you may have on those date columns. Since your example was only a snippet I can only provide a snipper in return. But something like this should be a lot easier and faster.

declare @DateCompare datetime = '20200101'

startdate <= @DateCompare
enddate >= @DateCompare

CodePudding user response：

The problem is the source - you shouldn't ever be storing dates as strings and especially not strings in a specific language.

If it always represents a single month, then you can do something like this:

DECLARE @monthid varchar(8) = 'jan 2020';

SET LANGUAGE us_english;
DECLARE @month date = TRY_CONVERT(date, '01 '   @monthid);

SELECT ... FROM ...
  WHERE col >= @month
  AND col < DATEADD(MONTH, 1, @month);

However I really think you need to fix how that jan 2020 is stored in the first place.