Checking a Partial Date

I have a string field called Period in a table that has values such as '2016-04'. I want to check for records that have the current period but not sure how to create todays period so the clause would be:

WHERE Period = Year(Getdate()) + '-' + Month(Getdate())

I need the expression after the '=' to evaluate to '2016-04'. Not sure how to do that. I also know if I use the month function I only get 4 returned instead of '04'

select convert(varchar(4),year(getdate()))+'-'+ case when len(convert(varchar(4),month(getdate()))) = 1 then '0'+convert(varchar(4),month(getdate())) else convert(varchar(4),month(getdate())) end

WHERE Period = CONVERT(varchar(7), Getdate(), 120)

1 Like

Scott has the best way. That 120 is Microsoft code for the date format and will not change.

Scott,

Thank you, yours was just what I needed.

Ross