SQL syntax error

[Ted Young]

I have the following SQL that is generating an error on the line after the
THEN (@Year =). I am getting an error that reads incorrect syntax near '='

Does anyone have any ideas or suggestions?

Thanks!

Ted


DECLARE @Year int
DECLARE @Month int
DECLARE @strYear char(4)
DECLARE @strMonth varchar(2)

UPDATE tblCommTransactions
SET EffectiveDate =
CASE
WHEN DatePart(mm,AnniversaryDate) > CONVERT (int,substring(PremiumMonth,5,2))
THEN
@Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
@Month = DatePart(mm,AnniversaryDate)
@strYear = CONVERT (char, @Year)
@strMonth = CONVERT (char, @Month)
CONVERT (datetime, @strYear + @strMonth + '01')
ELSE
@Year = CONVERT (int, substring(PremiumMonth, 1,4))
@Month = DatePart(mm,AnniversaryDate)
@strYear = CONVERT (char, @Year)
@strMonth = CONVERT (char, @Month)
CONVERT (datetime, @strYear + @strMonth + '01')
END
FROM tblCommTransactions a, tblGroupProfile b
WHERE a.AccountNum = b.GroupNum AND a.PremiumMonth is NOT Null AND b.AnniversaryDate
is NOT Null

[DaveSatz]

hard to tell w/o the DDL for tblCommTransactions and tblGroupProfile...

"Ted Young" <tedyoung@connecticare.com> wrote in message
news:3b6ffd83$1@news.devx.com...
>
> I have the following SQL that is generating an error on the line after the
> THEN (@Year =). I am getting an error that reads incorrect syntax near
'='
>
> Does anyone have any ideas or suggestions?
>
> Thanks!
>
> Ted
>
>
> DECLARE @Year int
> DECLARE @Month int
> DECLARE @strYear char(4)
> DECLARE @strMonth varchar(2)
>
> UPDATE tblCommTransactions
> SET EffectiveDate =
> CASE
> WHEN DatePart(mm,AnniversaryDate) > CONVERT
(int,substring(PremiumMonth,5,2))
> THEN
> @Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
> @Month = DatePart(mm,AnniversaryDate)
> @strYear = CONVERT (char, @Year)
> @strMonth = CONVERT (char, @Month)
> CONVERT (datetime, @strYear + @strMonth + '01')
> ELSE
> @Year = CONVERT (int, substring(PremiumMonth, 1,4))
> @Month = DatePart(mm,AnniversaryDate)
> @strYear = CONVERT (char, @Year)
> @strMonth = CONVERT (char, @Month)
> CONVERT (datetime, @strYear + @strMonth + '01')
> END
> FROM tblCommTransactions a, tblGroupProfile b
> WHERE a.AccountNum = b.GroupNum AND a.PremiumMonth is NOT Null AND
b.AnniversaryDate
> is NOT Null

[Ted Young]

Dave,

The error is a syntax error when I am trying to set my variables.

Maybe I should re-phrase the question to How do I set variables I declare?

The variables I am trying to set are within the CASE statement (not sure
if that matters).

Thanks!

"DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
>hard to tell w/o the DDL for tblCommTransactions and tblGroupProfile...
>
>"Ted Young" <tedyoung@connecticare.com> wrote in message
>news:3b6ffd83$1@news.devx.com...
>>
>> I have the following SQL that is generating an error on the line after
the
>> THEN (@Year =). I am getting an error that reads incorrect syntax near
>'='
>>
>> Does anyone have any ideas or suggestions?
>>
>> Thanks!
>>
>> Ted
>>
>>
>> DECLARE @Year int
>> DECLARE @Month int
>> DECLARE @strYear char(4)
>> DECLARE @strMonth varchar(2)
>>
>> UPDATE tblCommTransactions
>> SET EffectiveDate =
>> CASE
>> WHEN DatePart(mm,AnniversaryDate) > CONVERT
>(int,substring(PremiumMonth,5,2))
>> THEN
>> @Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
>> @Month = DatePart(mm,AnniversaryDate)
>> @strYear = CONVERT (char, @Year)
>> @strMonth = CONVERT (char, @Month)
>> CONVERT (datetime, @strYear + @strMonth + '01')
>> ELSE
>> @Year = CONVERT (int, substring(PremiumMonth, 1,4))
>> @Month = DatePart(mm,AnniversaryDate)
>> @strYear = CONVERT (char, @Year)
>> @strMonth = CONVERT (char, @Month)
>> CONVERT (datetime, @strYear + @strMonth + '01')
>> END
>> FROM tblCommTransactions a, tblGroupProfile b
>> WHERE a.AccountNum = b.GroupNum AND a.PremiumMonth is NOT Null AND
>b.AnniversaryDate
>> is NOT Null
>
>

[Ted Young]

Dave,

Could the problem be I have more than 1 statement in my CASE block?

I tried adding a BEGIN/END but that did not help.


"DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
>hard to tell w/o the DDL for tblCommTransactions and tblGroupProfile...
>
>"Ted Young" <tedyoung@connecticare.com> wrote in message
>news:3b6ffd83$1@news.devx.com...
>>
>> I have the following SQL that is generating an error on the line after
the
>> THEN (@Year =). I am getting an error that reads incorrect syntax near
>'='
>>
>> Does anyone have any ideas or suggestions?
>>
>> Thanks!
>>
>> Ted
>>
>>
>> DECLARE @Year int
>> DECLARE @Month int
>> DECLARE @strYear char(4)
>> DECLARE @strMonth varchar(2)
>>
>> UPDATE tblCommTransactions
>> SET EffectiveDate =
>> CASE
>> WHEN DatePart(mm,AnniversaryDate) > CONVERT
>(int,substring(PremiumMonth,5,2))
>> THEN
>> @Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
>> @Month = DatePart(mm,AnniversaryDate)
>> @strYear = CONVERT (char, @Year)
>> @strMonth = CONVERT (char, @Month)
>> CONVERT (datetime, @strYear + @strMonth + '01')
>> ELSE
>> @Year = CONVERT (int, substring(PremiumMonth, 1,4))
>> @Month = DatePart(mm,AnniversaryDate)
>> @strYear = CONVERT (char, @Year)
>> @strMonth = CONVERT (char, @Month)
>> CONVERT (datetime, @strYear + @strMonth + '01')
>> END
>> FROM tblCommTransactions a, tblGroupProfile b
>> WHERE a.AccountNum = b.GroupNum AND a.PremiumMonth is NOT Null AND
>b.AnniversaryDate
>> is NOT Null
>
>

[Ted Young]

Dave,

I added a BEGIN/END block and now I am getting an error message that
states INCORRECT SYNTAX NEAR KEYWORD BEGIN.

I think the issue is how to more than 1 statment in the CASE block

My SQL is:

DECLARE @Year int
DECLARE @Month int
DECLARE @strYear char(4)
DECLARE @strMonth varchar(2)

UPDATE tblCommTransactions
SET EffectiveDate =
CASE
WHEN DatePart(mm,AnniversaryDate) > CONVERT (int,substring(PremiumMonth,5,2))
THEN
BEGIN
@Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
@Month = DatePart(mm,AnniversaryDate)
@strYear = CONVERT (char, @Year)
@strMonth = right("0" + CONVERT (char, @Month),2)
CONVERT (datetime, @strYear + @strMonth + '01')
END
ELSE
BEGIN
@Year = CONVERT (int, substring(PremiumMonth, 1,4))
@Month = DatePart(mm,AnniversaryDate)
@strYear = CONVERT (char, @Year)
@strMonth = right("0" + CONVERT (char, @Month),2)
CONVERT (datetime, @strYear + @strMonth + '01')
END
END
FROM tblCommTransactions a, Enterprise..tblGroupProfile b
WHERE a.AccountNum = b.GroupNum AND a.PremiumMonth is NOT Null AND b.AnniversaryDate
is NOT Null


"Ted Young" <tedyoung@connecticare.com> wrote:
>
>Dave,
>
>Could the problem be I have more than 1 statement in my CASE block?
>
>I tried adding a BEGIN/END but that did not help.
>
>
>"DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
>>hard to tell w/o the DDL for tblCommTransactions and tblGroupProfile...
>>
>>"Ted Young" <tedyoung@connecticare.com> wrote in message
>>news:3b6ffd83$1@news.devx.com...
>>>
>>> I have the following SQL that is generating an error on the line after
>the
>>> THEN (@Year =). I am getting an error that reads incorrect syntax near
>>'='
>>>
>>> Does anyone have any ideas or suggestions?
>>>
>>> Thanks!
>>>
>>> Ted
>>>
>>>
>>> DECLARE @Year int
>>> DECLARE @Month int
>>> DECLARE @strYear char(4)
>>> DECLARE @strMonth varchar(2)
>>>
>>> UPDATE tblCommTransactions
>>> SET EffectiveDate =
>>> CASE
>>> WHEN DatePart(mm,AnniversaryDate) > CONVERT
>>(int,substring(PremiumMonth,5,2))
>>> THEN
>>> @Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
>>> @Month = DatePart(mm,AnniversaryDate)
>>> @strYear = CONVERT (char, @Year)
>>> @strMonth = CONVERT (char, @Month)
>>> CONVERT (datetime, @strYear + @strMonth + '01')
>>> ELSE
>>> @Year = CONVERT (int, substring(PremiumMonth, 1,4))
>>> @Month = DatePart(mm,AnniversaryDate)
>>> @strYear = CONVERT (char, @Year)
>>> @strMonth = CONVERT (char, @Month)
>>> CONVERT (datetime, @strYear + @strMonth + '01')
>>> END
>>> FROM tblCommTransactions a, tblGroupProfile b
>>> WHERE a.AccountNum = b.GroupNum AND a.PremiumMonth is NOT Null AND
>>b.AnniversaryDate
>>> is NOT Null
>>
>>
>

[DaveSatz]

Ted,

Not sure what you are trying to do since UPDATE tblCommTransactions SET
EffectiveDate = ... the case statement must return a date(time) value to
update the EffectiveDate column with. So all this code:

BEGIN
@Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
@Month = DatePart(mm,AnniversaryDate)
@strYear = CONVERT (char, @Year)
@strMonth = right("0" + CONVERT (char, @Month),2)
CONVERT (datetime, @strYear + @strMonth + '01')
END

must be like

UPDATE tblCommTransactions
SET EffectiveDate = ( CASE
WHEN DatePart(mm,AnniversaryDate) > CONVERT
(int,substring(PremiumMonth,5,2))
THEN some discreet datetime value
ELSE some discreet datetime value
END )
......
hth

"Ted Young" <tedyoung@connecticare.com> wrote in message
news:3b700a97$1@news.devx.com...
>
> Dave,
>
> I added a BEGIN/END block and now I am getting an error message that
> states INCORRECT SYNTAX NEAR KEYWORD BEGIN.
>
> I think the issue is how to more than 1 statment in the CASE block
>
> My SQL is:
>
> DECLARE @Year int
> DECLARE @Month int
> DECLARE @strYear char(4)
> DECLARE @strMonth varchar(2)
>
> UPDATE tblCommTransactions
> SET EffectiveDate =
> CASE
> WHEN DatePart(mm,AnniversaryDate) > CONVERT
(int,substring(PremiumMonth,5,2))
> THEN
> BEGIN
> @Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
> @Month = DatePart(mm,AnniversaryDate)
> @strYear = CONVERT (char, @Year)
> @strMonth = right("0" + CONVERT (char, @Month),2)
> CONVERT (datetime, @strYear + @strMonth + '01')
> END
> ELSE
> BEGIN
> @Year = CONVERT (int, substring(PremiumMonth, 1,4))
> @Month = DatePart(mm,AnniversaryDate)
> @strYear = CONVERT (char, @Year)
> @strMonth = right("0" + CONVERT (char, @Month),2)
> CONVERT (datetime, @strYear + @strMonth + '01')
> END
> END
> FROM tblCommTransactions a, Enterprise..tblGroupProfile b
> WHERE a.AccountNum = b.GroupNum AND a.PremiumMonth is NOT Null AND
b.AnniversaryDate
> is NOT Null
>
>
> "Ted Young" <tedyoung@connecticare.com> wrote:
> >
> >Dave,
> >
> >Could the problem be I have more than 1 statement in my CASE block?
> >
> >I tried adding a BEGIN/END but that did not help.
> >
> >
> >"DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
> >>hard to tell w/o the DDL for tblCommTransactions and tblGroupProfile...
> >>
> >>"Ted Young" <tedyoung@connecticare.com> wrote in message
> >>news:3b6ffd83$1@news.devx.com...
> >>>
> >>> I have the following SQL that is generating an error on the line after
> >the
> >>> THEN (@Year =). I am getting an error that reads incorrect syntax
near
> >>'='
> >>>
> >>> Does anyone have any ideas or suggestions?
> >>>
> >>> Thanks!
> >>>
> >>> Ted
> >>>
> >>>
> >>> DECLARE @Year int
> >>> DECLARE @Month int
> >>> DECLARE @strYear char(4)
> >>> DECLARE @strMonth varchar(2)
> >>>
> >>> UPDATE tblCommTransactions
> >>> SET EffectiveDate =
> >>> CASE
> >>> WHEN DatePart(mm,AnniversaryDate) > CONVERT
> >>(int,substring(PremiumMonth,5,2))
> >>> THEN
> >>> @Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
> >>> @Month = DatePart(mm,AnniversaryDate)
> >>> @strYear = CONVERT (char, @Year)
> >>> @strMonth = CONVERT (char, @Month)
> >>> CONVERT (datetime, @strYear + @strMonth + '01')
> >>> ELSE
> >>> @Year = CONVERT (int, substring(PremiumMonth, 1,4))
> >>> @Month = DatePart(mm,AnniversaryDate)
> >>> @strYear = CONVERT (char, @Year)
> >>> @strMonth = CONVERT (char, @Month)
> >>> CONVERT (datetime, @strYear + @strMonth + '01')
> >>> END
> >>> FROM tblCommTransactions a, tblGroupProfile b
> >>> WHERE a.AccountNum = b.GroupNum AND a.PremiumMonth is NOT Null AND
> >>b.AnniversaryDate
> >>> is NOT Null
> >>
> >>
> >
>

[Ted Young]

Dave,

Thanks for the feedback!

The code within the begin/end block is used to set some variables that the
CONVERT line uses to calculate the date.

Basically my calculation has several steps, that is why I am using the BEGIN/END
Block.

Does this make sense?

My case statement will return a value but FIRST I need to perform some calculations.

Is this possible?

Thanks!

Ted

"DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
>Ted,
>
>Not sure what you are trying to do since UPDATE tblCommTransactions SET
>EffectiveDate = ... the case statement must return a date(time) value to
>update the EffectiveDate column with. So all this code:
>
>BEGIN
> @Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
> @Month = DatePart(mm,AnniversaryDate)
> @strYear = CONVERT (char, @Year)
> @strMonth = right("0" + CONVERT (char, @Month),2)
> CONVERT (datetime, @strYear + @strMonth + '01')
>END
>
>must be like
>
>UPDATE tblCommTransactions
>SET EffectiveDate = ( CASE
> WHEN DatePart(mm,AnniversaryDate) > CONVERT
>(int,substring(PremiumMonth,5,2))
> THEN some discreet datetime value
> ELSE some discreet datetime value
> END )
>......
>hth
>
>"Ted Young" <tedyoung@connecticare.com> wrote in message
>news:3b700a97$1@news.devx.com...
>>
>> Dave,
>>
>> I added a BEGIN/END block and now I am getting an error message that
>> states INCORRECT SYNTAX NEAR KEYWORD BEGIN.
>>
>> I think the issue is how to more than 1 statment in the CASE block
>>
>> My SQL is:
>>
>> DECLARE @Year int
>> DECLARE @Month int
>> DECLARE @strYear char(4)
>> DECLARE @strMonth varchar(2)
>>
>> UPDATE tblCommTransactions
>> SET EffectiveDate =
>> CASE
>> WHEN DatePart(mm,AnniversaryDate) > CONVERT
>(int,substring(PremiumMonth,5,2))
>> THEN
>> BEGIN
>> @Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
>> @Month = DatePart(mm,AnniversaryDate)
>> @strYear = CONVERT (char, @Year)
>> @strMonth = right("0" + CONVERT (char, @Month),2)
>> CONVERT (datetime, @strYear + @strMonth + '01')
>> END
>> ELSE
>> BEGIN
>> @Year = CONVERT (int, substring(PremiumMonth, 1,4))
>> @Month = DatePart(mm,AnniversaryDate)
>> @strYear = CONVERT (char, @Year)
>> @strMonth = right("0" + CONVERT (char, @Month),2)
>> CONVERT (datetime, @strYear + @strMonth + '01')
>> END
>> END
>> FROM tblCommTransactions a, Enterprise..tblGroupProfile b
>> WHERE a.AccountNum = b.GroupNum AND a.PremiumMonth is NOT Null AND
>b.AnniversaryDate
>> is NOT Null
>>
>>
>> "Ted Young" <tedyoung@connecticare.com> wrote:
>> >
>> >Dave,
>> >
>> >Could the problem be I have more than 1 statement in my CASE block?
>> >
>> >I tried adding a BEGIN/END but that did not help.
>> >
>> >
>> >"DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
>> >>hard to tell w/o the DDL for tblCommTransactions and tblGroupProfile...
>> >>
>> >>"Ted Young" <tedyoung@connecticare.com> wrote in message
>> >>news:3b6ffd83$1@news.devx.com...
>> >>>
>> >>> I have the following SQL that is generating an error on the line after
>> >the
>> >>> THEN (@Year =). I am getting an error that reads incorrect syntax
>near
>> >>'='
>> >>>
>> >>> Does anyone have any ideas or suggestions?
>> >>>
>> >>> Thanks!
>> >>>
>> >>> Ted
>> >>>
>> >>>
>> >>> DECLARE @Year int
>> >>> DECLARE @Month int
>> >>> DECLARE @strYear char(4)
>> >>> DECLARE @strMonth varchar(2)
>> >>>
>> >>> UPDATE tblCommTransactions
>> >>> SET EffectiveDate =
>> >>> CASE
>> >>> WHEN DatePart(mm,AnniversaryDate) > CONVERT
>> >>(int,substring(PremiumMonth,5,2))
>> >>> THEN
>> >>> @Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
>> >>> @Month = DatePart(mm,AnniversaryDate)
>> >>> @strYear = CONVERT (char, @Year)
>> >>> @strMonth = CONVERT (char, @Month)
>> >>> CONVERT (datetime, @strYear + @strMonth + '01')
>> >>> ELSE
>> >>> @Year = CONVERT (int, substring(PremiumMonth, 1,4))
>> >>> @Month = DatePart(mm,AnniversaryDate)
>> >>> @strYear = CONVERT (char, @Year)
>> >>> @strMonth = CONVERT (char, @Month)
>> >>> CONVERT (datetime, @strYear + @strMonth + '01')
>> >>> END
>> >>> FROM tblCommTransactions a, tblGroupProfile b
>> >>> WHERE a.AccountNum = b.GroupNum AND a.PremiumMonth is NOT Null AND
>> >>b.AnniversaryDate
>> >>> is NOT Null
>> >>
>> >>
>> >
>>
>
>

[DaveSatz]

It is only possible within an UPDATE statement if you are using SQL 2000 and
you create a user defined function to return the datetime.

"Ted Young" <tedyoung@connecticare.com> wrote in message
news:3b7043b8$1@news.devx.com...
>
> Dave,
>
> Thanks for the feedback!
>
> The code within the begin/end block is used to set some variables that the
> CONVERT line uses to calculate the date.
>
> Basically my calculation has several steps, that is why I am using the
BEGIN/END
> Block.
>
> Does this make sense?
>
> My case statement will return a value but FIRST I need to perform some
calculations.
>
> Is this possible?
>
> Thanks!
>
> Ted
>
> "DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
> >Ted,
> >
> >Not sure what you are trying to do since UPDATE tblCommTransactions SET
> >EffectiveDate = ... the case statement must return a date(time) value to
> >update the EffectiveDate column with. So all this code:
> >
> >BEGIN
> > @Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
> > @Month = DatePart(mm,AnniversaryDate)
> > @strYear = CONVERT (char, @Year)
> > @strMonth = right("0" + CONVERT (char, @Month),2)
> > CONVERT (datetime, @strYear + @strMonth + '01')
> >END
> >
> >must be like
> >
> >UPDATE tblCommTransactions
> >SET EffectiveDate = ( CASE
> > WHEN DatePart(mm,AnniversaryDate) > CONVERT
> >(int,substring(PremiumMonth,5,2))
> > THEN some discreet datetime value
> > ELSE some discreet datetime value
> > END )
> >......
> >hth
> >
> >"Ted Young" <tedyoung@connecticare.com> wrote in message
> >news:3b700a97$1@news.devx.com...
> >>
> >> Dave,
> >>
> >> I added a BEGIN/END block and now I am getting an error message that
> >> states INCORRECT SYNTAX NEAR KEYWORD BEGIN.
> >>
> >> I think the issue is how to more than 1 statment in the CASE block
> >>
> >> My SQL is:
> >>
> >> DECLARE @Year int
> >> DECLARE @Month int
> >> DECLARE @strYear char(4)
> >> DECLARE @strMonth varchar(2)
> >>
> >> UPDATE tblCommTransactions
> >> SET EffectiveDate =
> >> CASE
> >> WHEN DatePart(mm,AnniversaryDate) > CONVERT
> >(int,substring(PremiumMonth,5,2))
> >> THEN
> >> BEGIN
> >> @Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
> >> @Month = DatePart(mm,AnniversaryDate)
> >> @strYear = CONVERT (char, @Year)
> >> @strMonth = right("0" + CONVERT (char, @Month),2)
> >> CONVERT (datetime, @strYear + @strMonth + '01')
> >> END
> >> ELSE
> >> BEGIN
> >> @Year = CONVERT (int, substring(PremiumMonth, 1,4))
> >> @Month = DatePart(mm,AnniversaryDate)
> >> @strYear = CONVERT (char, @Year)
> >> @strMonth = right("0" + CONVERT (char, @Month),2)
> >> CONVERT (datetime, @strYear + @strMonth + '01')
> >> END
> >> END
> >> FROM tblCommTransactions a, Enterprise..tblGroupProfile b
> >> WHERE a.AccountNum = b.GroupNum AND a.PremiumMonth is NOT Null AND
> >b.AnniversaryDate
> >> is NOT Null
> >>
> >>
> >> "Ted Young" <tedyoung@connecticare.com> wrote:
> >> >
> >> >Dave,
> >> >
> >> >Could the problem be I have more than 1 statement in my CASE block?
> >> >
> >> >I tried adding a BEGIN/END but that did not help.
> >> >
> >> >
> >> >"DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
> >> >>hard to tell w/o the DDL for tblCommTransactions and
tblGroupProfile...
> >> >>
> >> >>"Ted Young" <tedyoung@connecticare.com> wrote in message
> >> >>news:3b6ffd83$1@news.devx.com...
> >> >>>
> >> >>> I have the following SQL that is generating an error on the line
after
> >> >the
> >> >>> THEN (@Year =). I am getting an error that reads incorrect syntax
> >near
> >> >>'='
> >> >>>
> >> >>> Does anyone have any ideas or suggestions?
> >> >>>
> >> >>> Thanks!
> >> >>>
> >> >>> Ted
> >> >>>
> >> >>>
> >> >>> DECLARE @Year int
> >> >>> DECLARE @Month int
> >> >>> DECLARE @strYear char(4)
> >> >>> DECLARE @strMonth varchar(2)
> >> >>>
> >> >>> UPDATE tblCommTransactions
> >> >>> SET EffectiveDate =
> >> >>> CASE
> >> >>> WHEN DatePart(mm,AnniversaryDate) > CONVERT
> >> >>(int,substring(PremiumMonth,5,2))
> >> >>> THEN
> >> >>> @Year = CONVERT (int, substring(PremiumMonth, 1,4)) -1
> >> >>> @Month = DatePart(mm,AnniversaryDate)
> >> >>> @strYear = CONVERT (char, @Year)
> >> >>> @strMonth = CONVERT (char, @Month)
> >> >>> CONVERT (datetime, @strYear + @strMonth + '01')
> >> >>> ELSE
> >> >>> @Year = CONVERT (int, substring(PremiumMonth, 1,4))
> >> >>> @Month = DatePart(mm,AnniversaryDate)
> >> >>> @strYear = CONVERT (char, @Year)
> >> >>> @strMonth = CONVERT (char, @Month)
> >> >>> CONVERT (datetime, @strYear + @strMonth + '01')
> >> >>> END
> >> >>> FROM tblCommTransactions a, tblGroupProfile b
> >> >>> WHERE a.AccountNum = b.GroupNum AND a.PremiumMonth is NOT Null AND
> >> >>b.AnniversaryDate
> >> >>> is NOT Null
> >> >>
> >> >>
> >> >
> >>
> >
> >
>