开发者

Put condition on select command : charindex - SQL

I need help with the following query. I want to put a condition on the returned value of select command which is an integer between 0 and any number.

DECLARE @month varchar(50);
set @month ='01-03-05-07-09-11'; <---  clarification: these are all months

Declare @cur_month int;
set @cur_month = Month(GetDate());

select charindex(str(@cur_month), @month);

I essentially want

If ( select charindex(str(@cur_month), @month))
    // successful

I get error

An expression of non-boolean type specified in a context where a condition is expected, near 'print'.

I tried CAST and CONVERT but to no Avail. As you can see, I am trying to see if the current month 开发者_如何学Pythonis in my @month field, I want to execute certain actions if it is.


Taking the answer from @PaulStock with a couple tweaks will work better.

Changes from PaulStock's answer

  • Left pad for leading zeros. 2 != 02 and wouldn't match.
  • Add hyphens at the beginning and end of the string so each value is in the form -XX- I included this in case you realize you won't be having the leading zeros. If you'll always have leading zeros you won't need this.

Actual code

DECLARE @month VARCHAR(50);
set @month ='03-05-12-09';

set @month = '-' + @month + '-'

IF CHARINDEX('-' + right('00' + CAST(MONTH('2/13/2011') AS VARCHAR), 2) + '-', @month) > 0
    PRINT 'exists'
ELSE 
    PRINT 'does not exist'


I don't know what variation on the SQL language you are using, since SQL does not support variables and IF statements, but why not use a variable to represent the result of the condition?

SELECT @MyResult=charindex(str(@cur_month), @month); 

-- Now you can write an IF statement using the variable
IF @MyResult...


The return value for select charindex is an integer, not a boolean type. You'll need to put a boolean into your if statement.

Try something like:

If ( select charindex(str(@cur_month), @month) > 0)


Instead of using string functions, use date functions since you are trying to compare one datefield to another. And use datetime for @month instead of varchar:

DECLARE @month datetime;
set @month ='09-03-05';

IF MONTH(@month) = MONTH(CURRENT_TIMESTAMP) 
    PRINT 'Same Month'

UPDATE

Based on your updated question, here is new code:

DECLARE @month VARCHAR(50);
set @month ='03-05-02-09';

IF CHARINDEX(CAST(MONTH(CURRENT_TIMESTAMP) AS VARCHAR), @month) > 0
    PRINT 'Same Month'


This solution pads a zero to the left for months < 10, to make sure it works for February (2) when december is on the list (12), as it was pointed out by OCary

DECLARE @month varchar(50);
set @month ='01-05-07-09-11-12';

If (charindex(right('0' + CAST(MONTH(getdate()) as varchar), 2), @month) > 0)
    PRINT 'Same Month'

if you run this:

select right('0' + CAST(MONTH(getdate()) as varchar), 2)

it returns 09

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜