Select statement with month & year as varchar 1

[DebbieC]

I have a varchar column in a table that holds the month and date of a report. In order to retrieve the reports that were created this month I need to do the following select statement:

Select *
From tDbhreports
Where RepPeriod = '10/2006'

However I don't want to hardcode the date. I need it to use the current month and year.

I searched the web and tried many options I found and none worked. Here is one of them:

Select *
From tDbhreports
WHERE RepPeriod = right(Convert(varchar(20),getdate(),3),5)

Can someone help me?

 

[gmmastros]

Select *
From tDbhreports
Where RepPeriod = Right('00' + Convert(VarChar(2), Month(GetDate())), 2) + '/' + Convert(VarChar(4), Year(GetDate()))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Or, from your code that you already found...

Select right(Convert(varchar(20),getdate(),[!]103[/!]),[!]7[/!])

By using your original 3, you were effectively using 2 digit year (06) instead of 2006. 103 essentially does the same thing, except 4 digits years are used instead. And, of course, since you now have 2 extra characters you want returned... the 5 was changed to a 7.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DebbieC]

I did the following and it worked:

Select *
From tDbhreports
Where RepPeriod = right(Convert(varchar(20),getdate(),103),7)

And thank you so much for going into detail about the code. I saw a site that mentioned using "101" but of course that didn't work for what I wanted because I didn't want the day included but I couldn't find anything that said what to use for just the month & year.

Thank you so much!!!