DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: SQL syntax error

  1. #1
    Ted Young Guest

    SQL syntax error


    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

  2. #2
    DaveSatz Guest

    Re: SQL syntax error

    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




  3. #3
    Ted Young Guest

    Re: SQL syntax error


    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

    >
    >



  4. #4
    Ted Young Guest

    Re: SQL syntax error


    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

    >
    >



  5. #5
    Ted Young Guest

    Re: SQL syntax error


    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

    >>
    >>

    >



  6. #6
    DaveSatz Guest

    Re: SQL syntax error

    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
    > >>
    > >>

    > >

    >




  7. #7
    Ted Young Guest

    Re: SQL syntax error


    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
    >> >>
    >> >>
    >> >

    >>

    >
    >



  8. #8
    DaveSatz Guest

    Re: SQL syntax error

    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
    > >> >>
    > >> >>
    > >> >
    > >>

    > >
    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center