I am receiving a Code in sms like "yyyy/mm-digit" (digit increments with every message). so i want to ensure that the code sent by user is exactly in that format otherwise another action. So how ?

Here is my code to check but it always execute else block, why ? Note: I want it for e.g. "2014/8-digit" kinda pattern , how ? (it changes with respect to year and month)

Here is my little effort but it's throwing dateitme conversion failed error plus it might have other issues too, Help

Alter PROCEDURE TestTrigger

     Begin Try
           Declare @msg as varchar(20)
           SELECT @msg = '2014/9-1'

           DECLARE @yyyymm varchar(255) = Convert(Varchar ,(cast(year(@msg) as varchar(255)) + '/' +
                                right(cast(month(@msg) as varchar(255)), 2)

                IF (@msg like @yyyymm + '-[0-9]%' and
                    @msg not like @yyyymm + '-%[^0-9]%'
                    Print 'Done'
                    Print 'Not Done'
     Select ERROR_MESSAGE() as ErrorMsg


I'm not sure what you are trying to do, but try something like this:

Declare @msg as varchar(20)

SELECT @msg = '2014/9-1'

declare @slash as int 
declare @minus as int 

select  @slash = charindex('/', @msg), @minus = charindex('-',@msg)

DECLARE @yyyymm varchar(6) 

select @yyyymm = convert(varchar(4),left(@msg,@slash)) + convert(varchar(2),substring(@msg,@slash +1 ,@minus-@slash-1))

select @yyyymm

Please note:
1) I just typed this and didn't test it, some typos might be found.
2) It is advised that you check that charindex has returned values and that @minus is greater than @slash if you expect it that way.
3) It is advised that you check that year and month are actually integers before doing anything with them
4) I know that although I'm using dynamic positions to substring and left, I'm assigning the values to a fixed length var, a) but I don't remember if varchar(@slash-1) would work and I'm too bored to test it b) It would make things even more complex for you and finally c) I'm not gonna test it while @yyyymm is only 6 chars long.

Good luck

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.