Date type format compare with varchar-CodePudding

I have declare @date varchar(6)= '202206' , And I need to compare with original date format type is date (yyyy-mm-dd) and update all 202206's data

I use code below

declare @date varchar(6)= '202206';
select *
from xxx
where 
   [DateTime] >= left(@date,4)   '-'   Right(@date,2)   '-01' and 
   [DateTime] <= left(@date,4)   '-'   Right(@date,2)   '-31'

But June doesn't have 31 days so it will error, how can I edit it?

CodePudding user response：

The EOMONTH() function is an option (I assume the [DateTime] column is a date column).

DECLARE @date varchar(6)= '202206';

SELECT *
FROM xxx
WHERE 
   [DateTime] >= CONVERT(date, @date   '01', 112) AND 
   [DateTime] <= EOMONTH(CONVERT(date, @date   '01', 112))

CodePudding user response：

Another option is to use the datepart function

DECLARE @date varchar(6)= '202206';

SELECT *
FROM   xxx
WHERE  datepart(year, [DateTime]) >= datepart(year, convert(date, @date   '01'))
AND    datepart(month, [DateTime]) <= datepart(month, convert(date, @date   '01'))

This would be a lot easier if you have option to make other variables

DECLARE @date varchar(6) = '202206';

declare @fulldate date = @date   '01'

SELECT *
FROM   xxx
WHERE  datepart(year, [DateTime]) >= datepart(year, @fulldate)
AND    datepart(month, [DateTime]) <= datepart(month, @fulldate)

or even

declare @year int = 2022
declare @month int = 6

SELECT *
FROM   xxx
WHERE  datepart(year, [DateTime]) >= @year
AND    datepart(month, [DateTime]) <= @month