Home All Groups Group Topic Archive Search About

Conversion failed when converting datetime from character string.

microsoft.public.sqlserver.programming
Author
11 Aug 2006 8:09 PM
Kyle Thering
i am having some problems with a stored procedure that i created its
giving me an
Msg 241, Level 16, State 1, Procedure AS400ImportCSSCT, Line 19
Conversion failed when converting datetime from character string.
error any information would be helpfull, what the SP is sposed to do is
delete all data that is in the current shift and then recopy it from a
linked server shift times are 6AM to 6PM and 6PM to 6AM
Any information would be helpful thanks
[dbo].[SGCGCUPPREP](
    [CP_PDSTAT] [char](1) NOT NULL,
    [CP_PDTYPE] [char](2) NOT NULL,
    [CP_PDSERL] [decimal](9, 0) NOT NULL,
    [CP_ICORDR] [char](7) NOT NULL,
    [CP_ICLINE] [char](4) NOT NULL,
    [CP_PDCUST] [decimal](5, 0) NOT NULL,
    [CP_PDSCHD] [char](5) NOT NULL,
    [CP_PDCTWC] [char](3) NOT NULL,
    [CP_PDSHFT] [char](1) NOT NULL,
    [CP_PDWS] [char](1) NOT NULL,
    [CP_PDQTY] [decimal](5, 0) NOT NULL,
    [CP_PDTDAT] [char](8) NOT NULL,
    [CP_PDTTIM] [char](8) NOT NULL,
    [CP_PDPBY] [char](14) NOT NULL,
    [CP_PDPTO] [decimal](2, 0) NOT NULL,
    [CP_PDRREJ] [char](1) NOT NULL,
    [CP_PDRCDE] [char](4) NOT NULL,
    [CP_PDRESN] [char](50) NOT NULL,
    [CP_PDTHIK] [decimal](3, 1) NOT NULL,
    [CP_PDCTCF] [char](4) NOT NULL,
    [CP_PDBLEG] [decimal](3, 0) NOT NULL,
    [CP_PDBNUM] [decimal](2, 0) NOT NULL,
    [CP_PDBDEN] [decimal](2, 0) NOT NULL,
    [CP_PDLLEG] [decimal](3, 0) NOT NULL,
    [CP_PDLNUM] [decimal](2, 0) NOT NULL,
    [CP_PDLDEN] [decimal](2, 0) NOT NULL,
    [CP_PDWHLC] [char](5) NOT NULL,
    [CP_PDSRCD] [char](3) NOT NULL,
    [CP_PDSERR] [decimal](9, 0) NOT NULL,
    [CP_PDDNAM] [char](10) NOT NULL,
    [CP_PDDNBR] [char](6) NOT NULL,
    [CP_PDDPGM] [char](10) NOT NULL,
    [CP_PDDUSR] [char](10) NOT NULL,
    [CP_PDDUTD] [char](8) NOT NULL,
    [CP_PDDUTM] [char](8) NOT NULL,
    [CP_PDPSTS] [char](1) NOT NULL,
    [CP_PDLTSF] [decimal](5, 2) NOT NULL,
    [CP_PDLTCD] [char](10) NOT NULL,
    [CP_PDLTDS] [char](50) NOT NULL,
    [CP_PDTTSF] [decimal](5, 2) NOT NULL,
    [CP_PDTTCD] [char](10) NOT NULL,
    [CP_PDTTDS] [char](50) NOT NULL,
    [CP_PDCTSF] [decimal](5, 2) NOT NULL,
    [CP_PDCTCD] [char](10) NOT NULL,
    [CP_PDCTDS] [char](50) NOT NULL,
    [CP_PDBTSF] [decimal](5, 2) NOT NULL,
    [CP_PDBTCD] [char](10) NOT NULL,
    [CP_PDBTDS] [char](50) NOT NULL,
    [CP_PDTSQF] [decimal](7, 2) NOT NULL,
    [CP_PDTSQA] [decimal](7, 2) NOT NULL,
    [CP_ODPSQF] [decimal](6, 4) NOT NULL,
    [CP_ODPRCU] [decimal](5, 2) NOT NULL,
    [CP_ODCSQF] [decimal](6, 4) NOT NULL,
    [CP_ODCSTU] [decimal](5, 2) NOT NULL,
    [CP_UPDMSG] [char](50) NOT NULL,
    [CP_UPDNAM] [char](10) NOT NULL,
    [CP_UPDNBR] [char](6) NOT NULL,
    [CP_UPDPGM] [char](10) NOT NULL,
    [CP_UPDUSR] [char](10) NOT NULL,
    [CP_UPDUTD] [char](10) NOT NULL,
    [CP_UPDUTM] [char](10) NOT NULL,
    [CP_RWTHIK] [decimal](3, 1) NOT NULL,
    [CP_RWCTCF] [char](4) NOT NULL,
    [CP_BCSTAT] [char](1) NOT NULL,
    [CP_PRSTAT] [char](1) NOT NULL,
    [CP_UPDATE] [char](8) NOT NULL
) ON [PRIMARY]

[dbo].[AS400ImportCSSCT]
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @GETDATE DATETIME
DECLARE @SQL nvarchar(2000)
DECLARE @shift1 nvarchar(20)
DECLARE @shift2 nvarchar(20)

SET @shift1 = '6:00:00'
SET @shift2 = '18:00:00'

SET @GETDATE = 'GETDATE()'

SET @SQL =
CASE
    WHEN DATEPART(HH, @GETDATE) >= 6 AND DATEPART(HH, @GETDATE) < 18 THEN
        'DELETE
        FROM CSSCT.dbo.SGCGCUPPREP
        WHERE
            CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
'+@shift1+' AND
            CP_PDTTIM < '+@shift2+'
        INSERT INTO CSSCT.dbo.SGCGCUPPREP
        SELECT AS400.*
        FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
        WHERE
            CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
'+@shift1+' AND
            CP_PDTTIM < '+@shift2
    WHEN DATEPART(HH, @GETDATE) < 6 THEN
        'DELETE
        FROM CSSCT.dbo.SGCGCUPPREP
        WHERE
            CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+', 112) AND
            CP_PDTTIM >= '+@shift2+'
        INSERT INTO CSSCT.dbo.SGCGCUPPREP
        SELECT AS400.*
        FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
        WHERE
            CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+'), 112) AND
            CP_PDTTIM >= '+@shift2
    WHEN DATEPART(HH, @GETDATE) >= 18 THEN
        N'DELETE
        FROM CSSCT.dbo.SGCGCUPPREP
        WHERE
           CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
           CP_PDTTIM >= '+@shift2+'
        SELECT AS400.*
        FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
        WHERE
           CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
           CP_PDTTIM >= '+@shift2
END

Exec sp_executesql @SQL

END

Author
11 Aug 2006 8:36 PM
Reg Besseling
Hi Kyle

It looks like you have put the getdate() function in quotes changing it into
a character value

change all your 'GETDATE()'  to getdate()

and  CONVERT(varchar, '+@GETDATE+', 112)  to  CONVERT(varchar, @GETDATE, 112)

HTH
--
Regards

Reg Besseling


Show quote
"Kyle Thering" wrote:

> i am having some problems with a stored procedure that i created its
> giving me an
> Msg 241, Level 16, State 1, Procedure AS400ImportCSSCT, Line 19
> Conversion failed when converting datetime from character string.
> error any information would be helpfull, what the SP is sposed to do is
> delete all data that is in the current shift and then recopy it from a
> linked server shift times are 6AM to 6PM and 6PM to 6AM
> Any information would be helpful thanks
> [dbo].[SGCGCUPPREP](
>     [CP_PDSTAT] [char](1) NOT NULL,
>     [CP_PDTYPE] [char](2) NOT NULL,
>     [CP_PDSERL] [decimal](9, 0) NOT NULL,
>     [CP_ICORDR] [char](7) NOT NULL,
>     [CP_ICLINE] [char](4) NOT NULL,
>     [CP_PDCUST] [decimal](5, 0) NOT NULL,
>     [CP_PDSCHD] [char](5) NOT NULL,
>     [CP_PDCTWC] [char](3) NOT NULL,
>     [CP_PDSHFT] [char](1) NOT NULL,
>     [CP_PDWS] [char](1) NOT NULL,
>     [CP_PDQTY] [decimal](5, 0) NOT NULL,
>     [CP_PDTDAT] [char](8) NOT NULL,
>     [CP_PDTTIM] [char](8) NOT NULL,
>     [CP_PDPBY] [char](14) NOT NULL,
>     [CP_PDPTO] [decimal](2, 0) NOT NULL,
>     [CP_PDRREJ] [char](1) NOT NULL,
>     [CP_PDRCDE] [char](4) NOT NULL,
>     [CP_PDRESN] [char](50) NOT NULL,
>     [CP_PDTHIK] [decimal](3, 1) NOT NULL,
>     [CP_PDCTCF] [char](4) NOT NULL,
>     [CP_PDBLEG] [decimal](3, 0) NOT NULL,
>     [CP_PDBNUM] [decimal](2, 0) NOT NULL,
>     [CP_PDBDEN] [decimal](2, 0) NOT NULL,
>     [CP_PDLLEG] [decimal](3, 0) NOT NULL,
>     [CP_PDLNUM] [decimal](2, 0) NOT NULL,
>     [CP_PDLDEN] [decimal](2, 0) NOT NULL,
>     [CP_PDWHLC] [char](5) NOT NULL,
>     [CP_PDSRCD] [char](3) NOT NULL,
>     [CP_PDSERR] [decimal](9, 0) NOT NULL,
>     [CP_PDDNAM] [char](10) NOT NULL,
>     [CP_PDDNBR] [char](6) NOT NULL,
>     [CP_PDDPGM] [char](10) NOT NULL,
>     [CP_PDDUSR] [char](10) NOT NULL,
>     [CP_PDDUTD] [char](8) NOT NULL,
>     [CP_PDDUTM] [char](8) NOT NULL,
>     [CP_PDPSTS] [char](1) NOT NULL,
>     [CP_PDLTSF] [decimal](5, 2) NOT NULL,
>     [CP_PDLTCD] [char](10) NOT NULL,
>     [CP_PDLTDS] [char](50) NOT NULL,
>     [CP_PDTTSF] [decimal](5, 2) NOT NULL,
>     [CP_PDTTCD] [char](10) NOT NULL,
>     [CP_PDTTDS] [char](50) NOT NULL,
>     [CP_PDCTSF] [decimal](5, 2) NOT NULL,
>     [CP_PDCTCD] [char](10) NOT NULL,
>     [CP_PDCTDS] [char](50) NOT NULL,
>     [CP_PDBTSF] [decimal](5, 2) NOT NULL,
>     [CP_PDBTCD] [char](10) NOT NULL,
>     [CP_PDBTDS] [char](50) NOT NULL,
>     [CP_PDTSQF] [decimal](7, 2) NOT NULL,
>     [CP_PDTSQA] [decimal](7, 2) NOT NULL,
>     [CP_ODPSQF] [decimal](6, 4) NOT NULL,
>     [CP_ODPRCU] [decimal](5, 2) NOT NULL,
>     [CP_ODCSQF] [decimal](6, 4) NOT NULL,
>     [CP_ODCSTU] [decimal](5, 2) NOT NULL,
>     [CP_UPDMSG] [char](50) NOT NULL,
>     [CP_UPDNAM] [char](10) NOT NULL,
>     [CP_UPDNBR] [char](6) NOT NULL,
>     [CP_UPDPGM] [char](10) NOT NULL,
>     [CP_UPDUSR] [char](10) NOT NULL,
>     [CP_UPDUTD] [char](10) NOT NULL,
>     [CP_UPDUTM] [char](10) NOT NULL,
>     [CP_RWTHIK] [decimal](3, 1) NOT NULL,
>     [CP_RWCTCF] [char](4) NOT NULL,
>     [CP_BCSTAT] [char](1) NOT NULL,
>     [CP_PRSTAT] [char](1) NOT NULL,
>     [CP_UPDATE] [char](8) NOT NULL
> ) ON [PRIMARY]
>
>  [dbo].[AS400ImportCSSCT]
> AS
> BEGIN
>
>     SET NOCOUNT ON;
>
> DECLARE @GETDATE DATETIME
> DECLARE @SQL nvarchar(2000)
> DECLARE @shift1 nvarchar(20)
> DECLARE @shift2 nvarchar(20)
>
> SET @shift1 = '6:00:00'
> SET @shift2 = '18:00:00'
>
> SET @GETDATE = 'GETDATE()'
>
> SET @SQL =
> CASE
>     WHEN DATEPART(HH, @GETDATE) >= 6 AND DATEPART(HH, @GETDATE) < 18 THEN
>         'DELETE
>         FROM CSSCT.dbo.SGCGCUPPREP
>         WHERE
>             CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
> '+@shift1+' AND
>             CP_PDTTIM < '+@shift2+'
>         INSERT INTO CSSCT.dbo.SGCGCUPPREP
>         SELECT AS400.*
>         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
>         WHERE
>             CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
> '+@shift1+' AND
>             CP_PDTTIM < '+@shift2
>     WHEN DATEPART(HH, @GETDATE) < 6 THEN
>         'DELETE
>         FROM CSSCT.dbo.SGCGCUPPREP
>         WHERE
>             CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+', 112) AND
>             CP_PDTTIM >= '+@shift2+'
>         INSERT INTO CSSCT.dbo.SGCGCUPPREP
>         SELECT AS400.*
>         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
>         WHERE
>             CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+'), 112) AND
>             CP_PDTTIM >= '+@shift2
>     WHEN DATEPART(HH, @GETDATE) >= 18 THEN
>         N'DELETE
>         FROM CSSCT.dbo.SGCGCUPPREP
>         WHERE
>            CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
>            CP_PDTTIM >= '+@shift2+'
>         SELECT AS400.*
>         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
>         WHERE
>            CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
>            CP_PDTTIM >= '+@shift2
> END
>
> Exec sp_executesql @SQL
>
> END
>
>
Author
11 Aug 2006 9:17 PM
Kyle Thering
thanks for the suggestion that did get rid of that error, but now i
have a new one

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ':'.
and ideas,

Thanks


Reg Besseling wrote:
Show quote
> Hi Kyle
>
> It looks like you have put the getdate() function in quotes changing it into
> a character value
>
> change all your 'GETDATE()'  to getdate()
>
> and  CONVERT(varchar, '+@GETDATE+', 112)  to  CONVERT(varchar, @GETDATE, 112)
>
> HTH
> --
> Regards
>
> Reg Besseling
>
>
> "Kyle Thering" wrote:
>
> > i am having some problems with a stored procedure that i created its
> > giving me an
> > Msg 241, Level 16, State 1, Procedure AS400ImportCSSCT, Line 19
> > Conversion failed when converting datetime from character string.
> > error any information would be helpfull, what the SP is sposed to do is
> > delete all data that is in the current shift and then recopy it from a
> > linked server shift times are 6AM to 6PM and 6PM to 6AM
> > Any information would be helpful thanks
> > [dbo].[SGCGCUPPREP](
> >     [CP_PDSTAT] [char](1) NOT NULL,
> >     [CP_PDTYPE] [char](2) NOT NULL,
> >     [CP_PDSERL] [decimal](9, 0) NOT NULL,
> >     [CP_ICORDR] [char](7) NOT NULL,
> >     [CP_ICLINE] [char](4) NOT NULL,
> >     [CP_PDCUST] [decimal](5, 0) NOT NULL,
> >     [CP_PDSCHD] [char](5) NOT NULL,
> >     [CP_PDCTWC] [char](3) NOT NULL,
> >     [CP_PDSHFT] [char](1) NOT NULL,
> >     [CP_PDWS] [char](1) NOT NULL,
> >     [CP_PDQTY] [decimal](5, 0) NOT NULL,
> >     [CP_PDTDAT] [char](8) NOT NULL,
> >     [CP_PDTTIM] [char](8) NOT NULL,
> >     [CP_PDPBY] [char](14) NOT NULL,
> >     [CP_PDPTO] [decimal](2, 0) NOT NULL,
> >     [CP_PDRREJ] [char](1) NOT NULL,
> >     [CP_PDRCDE] [char](4) NOT NULL,
> >     [CP_PDRESN] [char](50) NOT NULL,
> >     [CP_PDTHIK] [decimal](3, 1) NOT NULL,
> >     [CP_PDCTCF] [char](4) NOT NULL,
> >     [CP_PDBLEG] [decimal](3, 0) NOT NULL,
> >     [CP_PDBNUM] [decimal](2, 0) NOT NULL,
> >     [CP_PDBDEN] [decimal](2, 0) NOT NULL,
> >     [CP_PDLLEG] [decimal](3, 0) NOT NULL,
> >     [CP_PDLNUM] [decimal](2, 0) NOT NULL,
> >     [CP_PDLDEN] [decimal](2, 0) NOT NULL,
> >     [CP_PDWHLC] [char](5) NOT NULL,
> >     [CP_PDSRCD] [char](3) NOT NULL,
> >     [CP_PDSERR] [decimal](9, 0) NOT NULL,
> >     [CP_PDDNAM] [char](10) NOT NULL,
> >     [CP_PDDNBR] [char](6) NOT NULL,
> >     [CP_PDDPGM] [char](10) NOT NULL,
> >     [CP_PDDUSR] [char](10) NOT NULL,
> >     [CP_PDDUTD] [char](8) NOT NULL,
> >     [CP_PDDUTM] [char](8) NOT NULL,
> >     [CP_PDPSTS] [char](1) NOT NULL,
> >     [CP_PDLTSF] [decimal](5, 2) NOT NULL,
> >     [CP_PDLTCD] [char](10) NOT NULL,
> >     [CP_PDLTDS] [char](50) NOT NULL,
> >     [CP_PDTTSF] [decimal](5, 2) NOT NULL,
> >     [CP_PDTTCD] [char](10) NOT NULL,
> >     [CP_PDTTDS] [char](50) NOT NULL,
> >     [CP_PDCTSF] [decimal](5, 2) NOT NULL,
> >     [CP_PDCTCD] [char](10) NOT NULL,
> >     [CP_PDCTDS] [char](50) NOT NULL,
> >     [CP_PDBTSF] [decimal](5, 2) NOT NULL,
> >     [CP_PDBTCD] [char](10) NOT NULL,
> >     [CP_PDBTDS] [char](50) NOT NULL,
> >     [CP_PDTSQF] [decimal](7, 2) NOT NULL,
> >     [CP_PDTSQA] [decimal](7, 2) NOT NULL,
> >     [CP_ODPSQF] [decimal](6, 4) NOT NULL,
> >     [CP_ODPRCU] [decimal](5, 2) NOT NULL,
> >     [CP_ODCSQF] [decimal](6, 4) NOT NULL,
> >     [CP_ODCSTU] [decimal](5, 2) NOT NULL,
> >     [CP_UPDMSG] [char](50) NOT NULL,
> >     [CP_UPDNAM] [char](10) NOT NULL,
> >     [CP_UPDNBR] [char](6) NOT NULL,
> >     [CP_UPDPGM] [char](10) NOT NULL,
> >     [CP_UPDUSR] [char](10) NOT NULL,
> >     [CP_UPDUTD] [char](10) NOT NULL,
> >     [CP_UPDUTM] [char](10) NOT NULL,
> >     [CP_RWTHIK] [decimal](3, 1) NOT NULL,
> >     [CP_RWCTCF] [char](4) NOT NULL,
> >     [CP_BCSTAT] [char](1) NOT NULL,
> >     [CP_PRSTAT] [char](1) NOT NULL,
> >     [CP_UPDATE] [char](8) NOT NULL
> > ) ON [PRIMARY]
> >
> >  [dbo].[AS400ImportCSSCT]
> > AS
> > BEGIN
> >
> >     SET NOCOUNT ON;
> >
> > DECLARE @GETDATE DATETIME
> > DECLARE @SQL nvarchar(2000)
> > DECLARE @shift1 nvarchar(20)
> > DECLARE @shift2 nvarchar(20)
> >
> > SET @shift1 = '6:00:00'
> > SET @shift2 = '18:00:00'
> >
> > SET @GETDATE = 'GETDATE()'
> >
> > SET @SQL =
> > CASE
> >     WHEN DATEPART(HH, @GETDATE) >= 6 AND DATEPART(HH, @GETDATE) < 18 THEN
> >         'DELETE
> >         FROM CSSCT.dbo.SGCGCUPPREP
> >         WHERE
> >             CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
> > '+@shift1+' AND
> >             CP_PDTTIM < '+@shift2+'
> >         INSERT INTO CSSCT.dbo.SGCGCUPPREP
> >         SELECT AS400.*
> >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> >         WHERE
> >             CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
> > '+@shift1+' AND
> >             CP_PDTTIM < '+@shift2
> >     WHEN DATEPART(HH, @GETDATE) < 6 THEN
> >         'DELETE
> >         FROM CSSCT.dbo.SGCGCUPPREP
> >         WHERE
> >             CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+', 112) AND
> >             CP_PDTTIM >= '+@shift2+'
> >         INSERT INTO CSSCT.dbo.SGCGCUPPREP
> >         SELECT AS400.*
> >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> >         WHERE
> >             CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+'), 112) AND
> >             CP_PDTTIM >= '+@shift2
> >     WHEN DATEPART(HH, @GETDATE) >= 18 THEN
> >         N'DELETE
> >         FROM CSSCT.dbo.SGCGCUPPREP
> >         WHERE
> >            CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
> >            CP_PDTTIM >= '+@shift2+'
> >         SELECT AS400.*
> >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> >         WHERE
> >            CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
> >            CP_PDTTIM >= '+@shift2
> > END
> >
> > Exec sp_executesql @SQL
> >
> > END
> >
> >
Author
11 Aug 2006 9:45 PM
Reg Besseling
OOps made a mistake i just looked for a conversion error and did not see you
were using dynamic sql. will hve another look and revert
--
Regards

Reg Besseling


Show quote
"Kyle Thering" wrote:

> thanks for the suggestion that did get rid of that error, but now i
> have a new one
>
> Msg 102, Level 15, State 1, Line 4
> Incorrect syntax near ':'.
> Msg 102, Level 15, State 1, Line 10
> Incorrect syntax near ':'.
> and ideas,
>
> Thanks
>
>
> Reg Besseling wrote:
> > Hi Kyle
> >
> > It looks like you have put the getdate() function in quotes changing it into
> > a character value
> >
> > change all your 'GETDATE()'  to getdate()
> >
> > and  CONVERT(varchar, '+@GETDATE+', 112)  to  CONVERT(varchar, @GETDATE, 112)
> >
> > HTH
> > --
> > Regards
> >
> > Reg Besseling
> >
> >
> > "Kyle Thering" wrote:
> >
> > > i am having some problems with a stored procedure that i created its
> > > giving me an
> > > Msg 241, Level 16, State 1, Procedure AS400ImportCSSCT, Line 19
> > > Conversion failed when converting datetime from character string.
> > > error any information would be helpfull, what the SP is sposed to do is
> > > delete all data that is in the current shift and then recopy it from a
> > > linked server shift times are 6AM to 6PM and 6PM to 6AM
> > > Any information would be helpful thanks
> > > [dbo].[SGCGCUPPREP](
> > >     [CP_PDSTAT] [char](1) NOT NULL,
> > >     [CP_PDTYPE] [char](2) NOT NULL,
> > >     [CP_PDSERL] [decimal](9, 0) NOT NULL,
> > >     [CP_ICORDR] [char](7) NOT NULL,
> > >     [CP_ICLINE] [char](4) NOT NULL,
> > >     [CP_PDCUST] [decimal](5, 0) NOT NULL,
> > >     [CP_PDSCHD] [char](5) NOT NULL,
> > >     [CP_PDCTWC] [char](3) NOT NULL,
> > >     [CP_PDSHFT] [char](1) NOT NULL,
> > >     [CP_PDWS] [char](1) NOT NULL,
> > >     [CP_PDQTY] [decimal](5, 0) NOT NULL,
> > >     [CP_PDTDAT] [char](8) NOT NULL,
> > >     [CP_PDTTIM] [char](8) NOT NULL,
> > >     [CP_PDPBY] [char](14) NOT NULL,
> > >     [CP_PDPTO] [decimal](2, 0) NOT NULL,
> > >     [CP_PDRREJ] [char](1) NOT NULL,
> > >     [CP_PDRCDE] [char](4) NOT NULL,
> > >     [CP_PDRESN] [char](50) NOT NULL,
> > >     [CP_PDTHIK] [decimal](3, 1) NOT NULL,
> > >     [CP_PDCTCF] [char](4) NOT NULL,
> > >     [CP_PDBLEG] [decimal](3, 0) NOT NULL,
> > >     [CP_PDBNUM] [decimal](2, 0) NOT NULL,
> > >     [CP_PDBDEN] [decimal](2, 0) NOT NULL,
> > >     [CP_PDLLEG] [decimal](3, 0) NOT NULL,
> > >     [CP_PDLNUM] [decimal](2, 0) NOT NULL,
> > >     [CP_PDLDEN] [decimal](2, 0) NOT NULL,
> > >     [CP_PDWHLC] [char](5) NOT NULL,
> > >     [CP_PDSRCD] [char](3) NOT NULL,
> > >     [CP_PDSERR] [decimal](9, 0) NOT NULL,
> > >     [CP_PDDNAM] [char](10) NOT NULL,
> > >     [CP_PDDNBR] [char](6) NOT NULL,
> > >     [CP_PDDPGM] [char](10) NOT NULL,
> > >     [CP_PDDUSR] [char](10) NOT NULL,
> > >     [CP_PDDUTD] [char](8) NOT NULL,
> > >     [CP_PDDUTM] [char](8) NOT NULL,
> > >     [CP_PDPSTS] [char](1) NOT NULL,
> > >     [CP_PDLTSF] [decimal](5, 2) NOT NULL,
> > >     [CP_PDLTCD] [char](10) NOT NULL,
> > >     [CP_PDLTDS] [char](50) NOT NULL,
> > >     [CP_PDTTSF] [decimal](5, 2) NOT NULL,
> > >     [CP_PDTTCD] [char](10) NOT NULL,
> > >     [CP_PDTTDS] [char](50) NOT NULL,
> > >     [CP_PDCTSF] [decimal](5, 2) NOT NULL,
> > >     [CP_PDCTCD] [char](10) NOT NULL,
> > >     [CP_PDCTDS] [char](50) NOT NULL,
> > >     [CP_PDBTSF] [decimal](5, 2) NOT NULL,
> > >     [CP_PDBTCD] [char](10) NOT NULL,
> > >     [CP_PDBTDS] [char](50) NOT NULL,
> > >     [CP_PDTSQF] [decimal](7, 2) NOT NULL,
> > >     [CP_PDTSQA] [decimal](7, 2) NOT NULL,
> > >     [CP_ODPSQF] [decimal](6, 4) NOT NULL,
> > >     [CP_ODPRCU] [decimal](5, 2) NOT NULL,
> > >     [CP_ODCSQF] [decimal](6, 4) NOT NULL,
> > >     [CP_ODCSTU] [decimal](5, 2) NOT NULL,
> > >     [CP_UPDMSG] [char](50) NOT NULL,
> > >     [CP_UPDNAM] [char](10) NOT NULL,
> > >     [CP_UPDNBR] [char](6) NOT NULL,
> > >     [CP_UPDPGM] [char](10) NOT NULL,
> > >     [CP_UPDUSR] [char](10) NOT NULL,
> > >     [CP_UPDUTD] [char](10) NOT NULL,
> > >     [CP_UPDUTM] [char](10) NOT NULL,
> > >     [CP_RWTHIK] [decimal](3, 1) NOT NULL,
> > >     [CP_RWCTCF] [char](4) NOT NULL,
> > >     [CP_BCSTAT] [char](1) NOT NULL,
> > >     [CP_PRSTAT] [char](1) NOT NULL,
> > >     [CP_UPDATE] [char](8) NOT NULL
> > > ) ON [PRIMARY]
> > >
> > >  [dbo].[AS400ImportCSSCT]
> > > AS
> > > BEGIN
> > >
> > >     SET NOCOUNT ON;
> > >
> > > DECLARE @GETDATE DATETIME
> > > DECLARE @SQL nvarchar(2000)
> > > DECLARE @shift1 nvarchar(20)
> > > DECLARE @shift2 nvarchar(20)
> > >
> > > SET @shift1 = '6:00:00'
> > > SET @shift2 = '18:00:00'
> > >
> > > SET @GETDATE = 'GETDATE()'
> > >
> > > SET @SQL =
> > > CASE
> > >     WHEN DATEPART(HH, @GETDATE) >= 6 AND DATEPART(HH, @GETDATE) < 18 THEN
> > >         'DELETE
> > >         FROM CSSCT.dbo.SGCGCUPPREP
> > >         WHERE
> > >             CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
> > > '+@shift1+' AND
> > >             CP_PDTTIM < '+@shift2+'
> > >         INSERT INTO CSSCT.dbo.SGCGCUPPREP
> > >         SELECT AS400.*
> > >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> > >         WHERE
> > >             CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
> > > '+@shift1+' AND
> > >             CP_PDTTIM < '+@shift2
> > >     WHEN DATEPART(HH, @GETDATE) < 6 THEN
> > >         'DELETE
> > >         FROM CSSCT.dbo.SGCGCUPPREP
> > >         WHERE
> > >             CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+', 112) AND
> > >             CP_PDTTIM >= '+@shift2+'
> > >         INSERT INTO CSSCT.dbo.SGCGCUPPREP
> > >         SELECT AS400.*
> > >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> > >         WHERE
> > >             CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+'), 112) AND
> > >             CP_PDTTIM >= '+@shift2
> > >     WHEN DATEPART(HH, @GETDATE) >= 18 THEN
> > >         N'DELETE
> > >         FROM CSSCT.dbo.SGCGCUPPREP
> > >         WHERE
> > >            CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
> > >            CP_PDTTIM >= '+@shift2+'
> > >         SELECT AS400.*
> > >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> > >         WHERE
> > >            CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
> > >            CP_PDTTIM >= '+@shift2
> > > END
> > >
> > > Exec sp_executesql @SQL
> > >
> > > END
> > >
> > >
>
>
Author
11 Aug 2006 10:08 PM
Reg Besseling
Kyle try this

DECLARE @GETDATE DATETIME
DECLARE @SQL nvarchar(2000)
DECLARE @shift1 nvarchar(20)
DECLARE @shift2 nvarchar(20)

SET @shift1 = '6:00:00'
SET @shift2 = '18:00:00'

SET @GETDATE = GETDATE()

SET @SQL =
CASE
WHEN DATEPART(HH, @GETDATE) >= 6 AND DATEPART(HH, @GETDATE) < 18 THEN
'DELETE
FROM CSSCT.dbo.SGCGCUPPREP
WHERE
CP_PDTDAT = '+  CONVERT(varchar, @GETDATE , 112)+ ' AND CP_PDTTIM >=
'+@shift1+' AND
CP_PDTTIM < '+@shift2+'
INSERT INTO CSSCT.dbo.SGCGCUPPREP
SELECT AS400.*
FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
WHERE
CP_PDTDAT = ' + CONVERT(varchar, @GETDATE, 112)+' AND CP_PDTTIM >=
'+@shift1+' AND
CP_PDTTIM < '+@shift2
WHEN DATEPART(HH, @GETDATE) < 6 THEN
'DELETE
FROM CSSCT.dbo.SGCGCUPPREP
WHERE
CP_PDTDAT = ' + CONVERT(varchar, DATEADD(d, -1, @GETDATE), 112) + ' AND
CP_PDTTIM >= '+@shift2+'
INSERT INTO CSSCT.dbo.SGCGCUPPREP
SELECT AS400.*
FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
WHERE
CP_PDTDAT = ' + CONVERT(varchar, ( DATEADD(dd, -1, @GETDATE)), 112) + ' AND
CP_PDTTIM >= '+@shift2
WHEN DATEPART(HH, @GETDATE) >= 18 THEN
N'DELETE
FROM CSSCT.dbo.SGCGCUPPREP
WHERE
CP_PDTDAT = '+ CONVERT(varchar, @GETDATE, 112)+' AND
CP_PDTTIM >= '+@shift2+'
SELECT AS400.*
FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
WHERE
CP_PDTDAT = '+ CONVERT(varchar, @GETDATE, 112)+' AND
CP_PDTTIM >= '+@shift2
END
Exec sp_executesql @SQL
--
Regards

Reg Besseling


Show quote
"Reg Besseling" wrote:

> OOps made a mistake i just looked for a conversion error and did not see you
> were using dynamic sql. will hve another look and revert
> --
> Regards
>
> Reg Besseling
>
>
> "Kyle Thering" wrote:
>
> > thanks for the suggestion that did get rid of that error, but now i
> > have a new one
> >
> > Msg 102, Level 15, State 1, Line 4
> > Incorrect syntax near ':'.
> > Msg 102, Level 15, State 1, Line 10
> > Incorrect syntax near ':'.
> > and ideas,
> >
> > Thanks
> >
> >
> > Reg Besseling wrote:
> > > Hi Kyle
> > >
> > > It looks like you have put the getdate() function in quotes changing it into
> > > a character value
> > >
> > > change all your 'GETDATE()'  to getdate()
> > >
> > > and  CONVERT(varchar, '+@GETDATE+', 112)  to  CONVERT(varchar, @GETDATE, 112)
> > >
> > > HTH
> > > --
> > > Regards
> > >
> > > Reg Besseling
> > >
> > >
> > > "Kyle Thering" wrote:
> > >
> > > > i am having some problems with a stored procedure that i created its
> > > > giving me an
> > > > Msg 241, Level 16, State 1, Procedure AS400ImportCSSCT, Line 19
> > > > Conversion failed when converting datetime from character string.
> > > > error any information would be helpfull, what the SP is sposed to do is
> > > > delete all data that is in the current shift and then recopy it from a
> > > > linked server shift times are 6AM to 6PM and 6PM to 6AM
> > > > Any information would be helpful thanks
> > > > [dbo].[SGCGCUPPREP](
> > > >     [CP_PDSTAT] [char](1) NOT NULL,
> > > >     [CP_PDTYPE] [char](2) NOT NULL,
> > > >     [CP_PDSERL] [decimal](9, 0) NOT NULL,
> > > >     [CP_ICORDR] [char](7) NOT NULL,
> > > >     [CP_ICLINE] [char](4) NOT NULL,
> > > >     [CP_PDCUST] [decimal](5, 0) NOT NULL,
> > > >     [CP_PDSCHD] [char](5) NOT NULL,
> > > >     [CP_PDCTWC] [char](3) NOT NULL,
> > > >     [CP_PDSHFT] [char](1) NOT NULL,
> > > >     [CP_PDWS] [char](1) NOT NULL,
> > > >     [CP_PDQTY] [decimal](5, 0) NOT NULL,
> > > >     [CP_PDTDAT] [char](8) NOT NULL,
> > > >     [CP_PDTTIM] [char](8) NOT NULL,
> > > >     [CP_PDPBY] [char](14) NOT NULL,
> > > >     [CP_PDPTO] [decimal](2, 0) NOT NULL,
> > > >     [CP_PDRREJ] [char](1) NOT NULL,
> > > >     [CP_PDRCDE] [char](4) NOT NULL,
> > > >     [CP_PDRESN] [char](50) NOT NULL,
> > > >     [CP_PDTHIK] [decimal](3, 1) NOT NULL,
> > > >     [CP_PDCTCF] [char](4) NOT NULL,
> > > >     [CP_PDBLEG] [decimal](3, 0) NOT NULL,
> > > >     [CP_PDBNUM] [decimal](2, 0) NOT NULL,
> > > >     [CP_PDBDEN] [decimal](2, 0) NOT NULL,
> > > >     [CP_PDLLEG] [decimal](3, 0) NOT NULL,
> > > >     [CP_PDLNUM] [decimal](2, 0) NOT NULL,
> > > >     [CP_PDLDEN] [decimal](2, 0) NOT NULL,
> > > >     [CP_PDWHLC] [char](5) NOT NULL,
> > > >     [CP_PDSRCD] [char](3) NOT NULL,
> > > >     [CP_PDSERR] [decimal](9, 0) NOT NULL,
> > > >     [CP_PDDNAM] [char](10) NOT NULL,
> > > >     [CP_PDDNBR] [char](6) NOT NULL,
> > > >     [CP_PDDPGM] [char](10) NOT NULL,
> > > >     [CP_PDDUSR] [char](10) NOT NULL,
> > > >     [CP_PDDUTD] [char](8) NOT NULL,
> > > >     [CP_PDDUTM] [char](8) NOT NULL,
> > > >     [CP_PDPSTS] [char](1) NOT NULL,
> > > >     [CP_PDLTSF] [decimal](5, 2) NOT NULL,
> > > >     [CP_PDLTCD] [char](10) NOT NULL,
> > > >     [CP_PDLTDS] [char](50) NOT NULL,
> > > >     [CP_PDTTSF] [decimal](5, 2) NOT NULL,
> > > >     [CP_PDTTCD] [char](10) NOT NULL,
> > > >     [CP_PDTTDS] [char](50) NOT NULL,
> > > >     [CP_PDCTSF] [decimal](5, 2) NOT NULL,
> > > >     [CP_PDCTCD] [char](10) NOT NULL,
> > > >     [CP_PDCTDS] [char](50) NOT NULL,
> > > >     [CP_PDBTSF] [decimal](5, 2) NOT NULL,
> > > >     [CP_PDBTCD] [char](10) NOT NULL,
> > > >     [CP_PDBTDS] [char](50) NOT NULL,
> > > >     [CP_PDTSQF] [decimal](7, 2) NOT NULL,
> > > >     [CP_PDTSQA] [decimal](7, 2) NOT NULL,
> > > >     [CP_ODPSQF] [decimal](6, 4) NOT NULL,
> > > >     [CP_ODPRCU] [decimal](5, 2) NOT NULL,
> > > >     [CP_ODCSQF] [decimal](6, 4) NOT NULL,
> > > >     [CP_ODCSTU] [decimal](5, 2) NOT NULL,
> > > >     [CP_UPDMSG] [char](50) NOT NULL,
> > > >     [CP_UPDNAM] [char](10) NOT NULL,
> > > >     [CP_UPDNBR] [char](6) NOT NULL,
> > > >     [CP_UPDPGM] [char](10) NOT NULL,
> > > >     [CP_UPDUSR] [char](10) NOT NULL,
> > > >     [CP_UPDUTD] [char](10) NOT NULL,
> > > >     [CP_UPDUTM] [char](10) NOT NULL,
> > > >     [CP_RWTHIK] [decimal](3, 1) NOT NULL,
> > > >     [CP_RWCTCF] [char](4) NOT NULL,
> > > >     [CP_BCSTAT] [char](1) NOT NULL,
> > > >     [CP_PRSTAT] [char](1) NOT NULL,
> > > >     [CP_UPDATE] [char](8) NOT NULL
> > > > ) ON [PRIMARY]
> > > >
> > > >  [dbo].[AS400ImportCSSCT]
> > > > AS
> > > > BEGIN
> > > >
> > > >     SET NOCOUNT ON;
> > > >
> > > > DECLARE @GETDATE DATETIME
> > > > DECLARE @SQL nvarchar(2000)
> > > > DECLARE @shift1 nvarchar(20)
> > > > DECLARE @shift2 nvarchar(20)
> > > >
> > > > SET @shift1 = '6:00:00'
> > > > SET @shift2 = '18:00:00'
> > > >
> > > > SET @GETDATE = 'GETDATE()'
> > > >
> > > > SET @SQL =
> > > > CASE
> > > >     WHEN DATEPART(HH, @GETDATE) >= 6 AND DATEPART(HH, @GETDATE) < 18 THEN
> > > >         'DELETE
> > > >         FROM CSSCT.dbo.SGCGCUPPREP
> > > >         WHERE
> > > >             CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
> > > > '+@shift1+' AND
> > > >             CP_PDTTIM < '+@shift2+'
> > > >         INSERT INTO CSSCT.dbo.SGCGCUPPREP
> > > >         SELECT AS400.*
> > > >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> > > >         WHERE
> > > >             CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
> > > > '+@shift1+' AND
> > > >             CP_PDTTIM < '+@shift2
> > > >     WHEN DATEPART(HH, @GETDATE) < 6 THEN
> > > >         'DELETE
> > > >         FROM CSSCT.dbo.SGCGCUPPREP
> > > >         WHERE
> > > >             CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+', 112) AND
> > > >             CP_PDTTIM >= '+@shift2+'
> > > >         INSERT INTO CSSCT.dbo.SGCGCUPPREP
> > > >         SELECT AS400.*
> > > >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> > > >         WHERE
> > > >             CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+'), 112) AND
> > > >             CP_PDTTIM >= '+@shift2
> > > >     WHEN DATEPART(HH, @GETDATE) >= 18 THEN
> > > >         N'DELETE
> > > >         FROM CSSCT.dbo.SGCGCUPPREP
> > > >         WHERE
> > > >            CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
> > > >            CP_PDTTIM >= '+@shift2+'
> > > >         SELECT AS400.*
> > > >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> > > >         WHERE
> > > >            CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
> > > >            CP_PDTTIM >= '+@shift2
> > > > END
> > > >
> > > > Exec sp_executesql @SQL
> > > >
> > > > END
> > > >
> > > >
> >
> >
Author
11 Aug 2006 9:59 PM
Michael Keating
Hi,

You are using @shift1 and @shift2 as litteral strings to be compared, so
they need to be quoted, as in;
CP_PDTTIM >= '+char(9)+@shift1+char(9)+' AND
CP_PDTTIM < '+char(9)+@shift2+char(9)+'
etc. for anywhere they are used as litterals.

Also the semi-colon after NOCOUNT is unnecessary, though I have no idea
whether it will produce an error.

MFK


Show quote
"Kyle Thering" <kther***@cardinalcorp.com> wrote in message
news:1155331028.316621.258910@m79g2000cwm.googlegroups.com...
> thanks for the suggestion that did get rid of that error, but now i
> have a new one
>
> Msg 102, Level 15, State 1, Line 4
> Incorrect syntax near ':'.
> Msg 102, Level 15, State 1, Line 10
> Incorrect syntax near ':'.
> and ideas,
>
> Thanks
>
>
> Reg Besseling wrote:
>> Hi Kyle
>>
>> It looks like you have put the getdate() function in quotes changing it
>> into
>> a character value
>>
>> change all your 'GETDATE()'  to getdate()
>>
>> and  CONVERT(varchar, '+@GETDATE+', 112)  to  CONVERT(varchar, @GETDATE,
>> 112)
>>
>> HTH
>> --
>> Regards
>>
>> Reg Besseling
>>
>>
>> "Kyle Thering" wrote:
>>
>> > i am having some problems with a stored procedure that i created its
>> > giving me an
>> > Msg 241, Level 16, State 1, Procedure AS400ImportCSSCT, Line 19
>> > Conversion failed when converting datetime from character string.
>> > error any information would be helpfull, what the SP is sposed to do is
>> > delete all data that is in the current shift and then recopy it from a
>> > linked server shift times are 6AM to 6PM and 6PM to 6AM
>> > Any information would be helpful thanks
>> > [dbo].[SGCGCUPPREP](
>> > [CP_PDSTAT] [char](1) NOT NULL,
>> > [CP_PDTYPE] [char](2) NOT NULL,
>> > [CP_PDSERL] [decimal](9, 0) NOT NULL,
>> > [CP_ICORDR] [char](7) NOT NULL,
>> > [CP_ICLINE] [char](4) NOT NULL,
>> > [CP_PDCUST] [decimal](5, 0) NOT NULL,
>> > [CP_PDSCHD] [char](5) NOT NULL,
>> > [CP_PDCTWC] [char](3) NOT NULL,
>> > [CP_PDSHFT] [char](1) NOT NULL,
>> > [CP_PDWS] [char](1) NOT NULL,
>> > [CP_PDQTY] [decimal](5, 0) NOT NULL,
>> > [CP_PDTDAT] [char](8) NOT NULL,
>> > [CP_PDTTIM] [char](8) NOT NULL,
>> > [CP_PDPBY] [char](14) NOT NULL,
>> > [CP_PDPTO] [decimal](2, 0) NOT NULL,
>> > [CP_PDRREJ] [char](1) NOT NULL,
>> > [CP_PDRCDE] [char](4) NOT NULL,
>> > [CP_PDRESN] [char](50) NOT NULL,
>> > [CP_PDTHIK] [decimal](3, 1) NOT NULL,
>> > [CP_PDCTCF] [char](4) NOT NULL,
>> > [CP_PDBLEG] [decimal](3, 0) NOT NULL,
>> > [CP_PDBNUM] [decimal](2, 0) NOT NULL,
>> > [CP_PDBDEN] [decimal](2, 0) NOT NULL,
>> > [CP_PDLLEG] [decimal](3, 0) NOT NULL,
>> > [CP_PDLNUM] [decimal](2, 0) NOT NULL,
>> > [CP_PDLDEN] [decimal](2, 0) NOT NULL,
>> > [CP_PDWHLC] [char](5) NOT NULL,
>> > [CP_PDSRCD] [char](3) NOT NULL,
>> > [CP_PDSERR] [decimal](9, 0) NOT NULL,
>> > [CP_PDDNAM] [char](10) NOT NULL,
>> > [CP_PDDNBR] [char](6) NOT NULL,
>> > [CP_PDDPGM] [char](10) NOT NULL,
>> > [CP_PDDUSR] [char](10) NOT NULL,
>> > [CP_PDDUTD] [char](8) NOT NULL,
>> > [CP_PDDUTM] [char](8) NOT NULL,
>> > [CP_PDPSTS] [char](1) NOT NULL,
>> > [CP_PDLTSF] [decimal](5, 2) NOT NULL,
>> > [CP_PDLTCD] [char](10) NOT NULL,
>> > [CP_PDLTDS] [char](50) NOT NULL,
>> > [CP_PDTTSF] [decimal](5, 2) NOT NULL,
>> > [CP_PDTTCD] [char](10) NOT NULL,
>> > [CP_PDTTDS] [char](50) NOT NULL,
>> > [CP_PDCTSF] [decimal](5, 2) NOT NULL,
>> > [CP_PDCTCD] [char](10) NOT NULL,
>> > [CP_PDCTDS] [char](50) NOT NULL,
>> > [CP_PDBTSF] [decimal](5, 2) NOT NULL,
>> > [CP_PDBTCD] [char](10) NOT NULL,
>> > [CP_PDBTDS] [char](50) NOT NULL,
>> > [CP_PDTSQF] [decimal](7, 2) NOT NULL,
>> > [CP_PDTSQA] [decimal](7, 2) NOT NULL,
>> > [CP_ODPSQF] [decimal](6, 4) NOT NULL,
>> > [CP_ODPRCU] [decimal](5, 2) NOT NULL,
>> > [CP_ODCSQF] [decimal](6, 4) NOT NULL,
>> > [CP_ODCSTU] [decimal](5, 2) NOT NULL,
>> > [CP_UPDMSG] [char](50) NOT NULL,
>> > [CP_UPDNAM] [char](10) NOT NULL,
>> > [CP_UPDNBR] [char](6) NOT NULL,
>> > [CP_UPDPGM] [char](10) NOT NULL,
>> > [CP_UPDUSR] [char](10) NOT NULL,
>> > [CP_UPDUTD] [char](10) NOT NULL,
>> > [CP_UPDUTM] [char](10) NOT NULL,
>> > [CP_RWTHIK] [decimal](3, 1) NOT NULL,
>> > [CP_RWCTCF] [char](4) NOT NULL,
>> > [CP_BCSTAT] [char](1) NOT NULL,
>> > [CP_PRSTAT] [char](1) NOT NULL,
>> > [CP_UPDATE] [char](8) NOT NULL
>> > ) ON [PRIMARY]
>> >
>> >  [dbo].[AS400ImportCSSCT]
>> > AS
>> > BEGIN
>> >
>> > SET NOCOUNT ON;
>> >
>> > DECLARE @GETDATE DATETIME
>> > DECLARE @SQL nvarchar(2000)
>> > DECLARE @shift1 nvarchar(20)
>> > DECLARE @shift2 nvarchar(20)
>> >
>> > SET @shift1 = '6:00:00'
>> > SET @shift2 = '18:00:00'
>> >
>> > SET @GETDATE = 'GETDATE()'
>> >
>> > SET @SQL =
>> > CASE
>> > WHEN DATEPART(HH, @GETDATE) >= 6 AND DATEPART(HH, @GETDATE) < 18 THEN
>> > 'DELETE
>> > FROM CSSCT.dbo.SGCGCUPPREP
>> > WHERE
>> > CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
>> > '+@shift1+' AND
>> > CP_PDTTIM < '+@shift2+'
>> > INSERT INTO CSSCT.dbo.SGCGCUPPREP
>> > SELECT AS400.*
>> > FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
>> > WHERE
>> > CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
>> > '+@shift1+' AND
>> > CP_PDTTIM < '+@shift2
>> > WHEN DATEPART(HH, @GETDATE) < 6 THEN
>> > 'DELETE
>> > FROM CSSCT.dbo.SGCGCUPPREP
>> > WHERE
>> > CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+', 112) AND
>> > CP_PDTTIM >= '+@shift2+'
>> > INSERT INTO CSSCT.dbo.SGCGCUPPREP
>> > SELECT AS400.*
>> > FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
>> > WHERE
>> > CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+'), 112) AND
>> > CP_PDTTIM >= '+@shift2
>> > WHEN DATEPART(HH, @GETDATE) >= 18 THEN
>> > N'DELETE
>> >         FROM CSSCT.dbo.SGCGCUPPREP
>> >         WHERE
>> >        CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
>> >            CP_PDTTIM >= '+@shift2+'
>> > SELECT AS400.*
>> > FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
>> > WHERE
>> >    CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
>> >            CP_PDTTIM >= '+@shift2
>> > END
>> >
>> > Exec sp_executesql @SQL
>> >
>> > END
>> >
>> >
>
Author
11 Aug 2006 10:09 PM
Kyle Thering
Thanks For who replied to me, i was able to get everything working,
thanks





Kyle Thering wrote:
Show quote
> thanks for the suggestion that did get rid of that error, but now i
> have a new one
>
> Msg 102, Level 15, State 1, Line 4
> Incorrect syntax near ':'.
> Msg 102, Level 15, State 1, Line 10
> Incorrect syntax near ':'.
> and ideas,
>
> Thanks
>
>
> Reg Besseling wrote:
> > Hi Kyle
> >
> > It looks like you have put the getdate() function in quotes changing it into
> > a character value
> >
> > change all your 'GETDATE()'  to getdate()
> >
> > and  CONVERT(varchar, '+@GETDATE+', 112)  to  CONVERT(varchar, @GETDATE, 112)
> >
> > HTH
> > --
> > Regards
> >
> > Reg Besseling
> >
> >
> > "Kyle Thering" wrote:
> >
> > > i am having some problems with a stored procedure that i created its
> > > giving me an
> > > Msg 241, Level 16, State 1, Procedure AS400ImportCSSCT, Line 19
> > > Conversion failed when converting datetime from character string.
> > > error any information would be helpfull, what the SP is sposed to do is
> > > delete all data that is in the current shift and then recopy it from a
> > > linked server shift times are 6AM to 6PM and 6PM to 6AM
> > > Any information would be helpful thanks
> > > [dbo].[SGCGCUPPREP](
> > >     [CP_PDSTAT] [char](1) NOT NULL,
> > >     [CP_PDTYPE] [char](2) NOT NULL,
> > >     [CP_PDSERL] [decimal](9, 0) NOT NULL,
> > >     [CP_ICORDR] [char](7) NOT NULL,
> > >     [CP_ICLINE] [char](4) NOT NULL,
> > >     [CP_PDCUST] [decimal](5, 0) NOT NULL,
> > >     [CP_PDSCHD] [char](5) NOT NULL,
> > >     [CP_PDCTWC] [char](3) NOT NULL,
> > >     [CP_PDSHFT] [char](1) NOT NULL,
> > >     [CP_PDWS] [char](1) NOT NULL,
> > >     [CP_PDQTY] [decimal](5, 0) NOT NULL,
> > >     [CP_PDTDAT] [char](8) NOT NULL,
> > >     [CP_PDTTIM] [char](8) NOT NULL,
> > >     [CP_PDPBY] [char](14) NOT NULL,
> > >     [CP_PDPTO] [decimal](2, 0) NOT NULL,
> > >     [CP_PDRREJ] [char](1) NOT NULL,
> > >     [CP_PDRCDE] [char](4) NOT NULL,
> > >     [CP_PDRESN] [char](50) NOT NULL,
> > >     [CP_PDTHIK] [decimal](3, 1) NOT NULL,
> > >     [CP_PDCTCF] [char](4) NOT NULL,
> > >     [CP_PDBLEG] [decimal](3, 0) NOT NULL,
> > >     [CP_PDBNUM] [decimal](2, 0) NOT NULL,
> > >     [CP_PDBDEN] [decimal](2, 0) NOT NULL,
> > >     [CP_PDLLEG] [decimal](3, 0) NOT NULL,
> > >     [CP_PDLNUM] [decimal](2, 0) NOT NULL,
> > >     [CP_PDLDEN] [decimal](2, 0) NOT NULL,
> > >     [CP_PDWHLC] [char](5) NOT NULL,
> > >     [CP_PDSRCD] [char](3) NOT NULL,
> > >     [CP_PDSERR] [decimal](9, 0) NOT NULL,
> > >     [CP_PDDNAM] [char](10) NOT NULL,
> > >     [CP_PDDNBR] [char](6) NOT NULL,
> > >     [CP_PDDPGM] [char](10) NOT NULL,
> > >     [CP_PDDUSR] [char](10) NOT NULL,
> > >     [CP_PDDUTD] [char](8) NOT NULL,
> > >     [CP_PDDUTM] [char](8) NOT NULL,
> > >     [CP_PDPSTS] [char](1) NOT NULL,
> > >     [CP_PDLTSF] [decimal](5, 2) NOT NULL,
> > >     [CP_PDLTCD] [char](10) NOT NULL,
> > >     [CP_PDLTDS] [char](50) NOT NULL,
> > >     [CP_PDTTSF] [decimal](5, 2) NOT NULL,
> > >     [CP_PDTTCD] [char](10) NOT NULL,
> > >     [CP_PDTTDS] [char](50) NOT NULL,
> > >     [CP_PDCTSF] [decimal](5, 2) NOT NULL,
> > >     [CP_PDCTCD] [char](10) NOT NULL,
> > >     [CP_PDCTDS] [char](50) NOT NULL,
> > >     [CP_PDBTSF] [decimal](5, 2) NOT NULL,
> > >     [CP_PDBTCD] [char](10) NOT NULL,
> > >     [CP_PDBTDS] [char](50) NOT NULL,
> > >     [CP_PDTSQF] [decimal](7, 2) NOT NULL,
> > >     [CP_PDTSQA] [decimal](7, 2) NOT NULL,
> > >     [CP_ODPSQF] [decimal](6, 4) NOT NULL,
> > >     [CP_ODPRCU] [decimal](5, 2) NOT NULL,
> > >     [CP_ODCSQF] [decimal](6, 4) NOT NULL,
> > >     [CP_ODCSTU] [decimal](5, 2) NOT NULL,
> > >     [CP_UPDMSG] [char](50) NOT NULL,
> > >     [CP_UPDNAM] [char](10) NOT NULL,
> > >     [CP_UPDNBR] [char](6) NOT NULL,
> > >     [CP_UPDPGM] [char](10) NOT NULL,
> > >     [CP_UPDUSR] [char](10) NOT NULL,
> > >     [CP_UPDUTD] [char](10) NOT NULL,
> > >     [CP_UPDUTM] [char](10) NOT NULL,
> > >     [CP_RWTHIK] [decimal](3, 1) NOT NULL,
> > >     [CP_RWCTCF] [char](4) NOT NULL,
> > >     [CP_BCSTAT] [char](1) NOT NULL,
> > >     [CP_PRSTAT] [char](1) NOT NULL,
> > >     [CP_UPDATE] [char](8) NOT NULL
> > > ) ON [PRIMARY]
> > >
> > >  [dbo].[AS400ImportCSSCT]
> > > AS
> > > BEGIN
> > >
> > >     SET NOCOUNT ON;
> > >
> > > DECLARE @GETDATE DATETIME
> > > DECLARE @SQL nvarchar(2000)
> > > DECLARE @shift1 nvarchar(20)
> > > DECLARE @shift2 nvarchar(20)
> > >
> > > SET @shift1 = '6:00:00'
> > > SET @shift2 = '18:00:00'
> > >
> > > SET @GETDATE = 'GETDATE()'
> > >
> > > SET @SQL =
> > > CASE
> > >     WHEN DATEPART(HH, @GETDATE) >= 6 AND DATEPART(HH, @GETDATE) < 18 THEN
> > >         'DELETE
> > >         FROM CSSCT.dbo.SGCGCUPPREP
> > >         WHERE
> > >             CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
> > > '+@shift1+' AND
> > >             CP_PDTTIM < '+@shift2+'
> > >         INSERT INTO CSSCT.dbo.SGCGCUPPREP
> > >         SELECT AS400.*
> > >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> > >         WHERE
> > >             CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
> > > '+@shift1+' AND
> > >             CP_PDTTIM < '+@shift2
> > >     WHEN DATEPART(HH, @GETDATE) < 6 THEN
> > >         'DELETE
> > >         FROM CSSCT.dbo.SGCGCUPPREP
> > >         WHERE
> > >             CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+', 112) AND
> > >             CP_PDTTIM >= '+@shift2+'
> > >         INSERT INTO CSSCT.dbo.SGCGCUPPREP
> > >         SELECT AS400.*
> > >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> > >         WHERE
> > >             CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+'), 112) AND
> > >             CP_PDTTIM >= '+@shift2
> > >     WHEN DATEPART(HH, @GETDATE) >= 18 THEN
> > >         N'DELETE
> > >         FROM CSSCT.dbo.SGCGCUPPREP
> > >         WHERE
> > >            CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
> > >            CP_PDTTIM >= '+@shift2+'
> > >         SELECT AS400.*
> > >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> > >         WHERE
> > >            CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
> > >            CP_PDTTIM >= '+@shift2
> > > END
> > >
> > > Exec sp_executesql @SQL
> > >
> > > END
> > >
> > >
Author
11 Aug 2006 10:10 PM
Reg Besseling
Kyle Try this

DECLARE @GETDATE DATETIME
DECLARE @SQL nvarchar(2000)
DECLARE @shift1 nvarchar(20)
DECLARE @shift2 nvarchar(20)

SET @shift1 = '6:00:00'
SET @shift2 = '18:00:00'

SET @GETDATE = GETDATE()

SET @SQL =
CASE
WHEN DATEPART(HH, @GETDATE) >= 6 AND DATEPART(HH, @GETDATE) < 18 THEN
'DELETE
FROM CSSCT.dbo.SGCGCUPPREP
WHERE
CP_PDTDAT = '+  CONVERT(varchar, @GETDATE , 112)+ ' AND CP_PDTTIM >=
'+@shift1+' AND
CP_PDTTIM < '+@shift2+'
INSERT INTO CSSCT.dbo.SGCGCUPPREP
SELECT AS400.*
FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
WHERE
CP_PDTDAT = ' + CONVERT(varchar, @GETDATE, 112)+' AND CP_PDTTIM >=
'+@shift1+' AND
CP_PDTTIM < '+@shift2
WHEN DATEPART(HH, @GETDATE) < 6 THEN
'DELETE
FROM CSSCT.dbo.SGCGCUPPREP
WHERE
CP_PDTDAT = ' + CONVERT(varchar, DATEADD(d, -1, @GETDATE), 112) + ' AND
CP_PDTTIM >= '+@shift2+'
INSERT INTO CSSCT.dbo.SGCGCUPPREP
SELECT AS400.*
FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
WHERE
CP_PDTDAT = ' + CONVERT(varchar, ( DATEADD(dd, -1, @GETDATE)), 112) + ' AND
CP_PDTTIM >= '+@shift2
WHEN DATEPART(HH, @GETDATE) >= 18 THEN
N'DELETE
FROM CSSCT.dbo.SGCGCUPPREP
WHERE
CP_PDTDAT = '+ CONVERT(varchar, @GETDATE, 112)+' AND
CP_PDTTIM >= '+@shift2+'
SELECT AS400.*
FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
WHERE
CP_PDTDAT = '+ CONVERT(varchar, @GETDATE, 112)+' AND
CP_PDTTIM >= '+@shift2
END

Exec sp_executesql @SQL
--
Regards

Reg Besseling


Show quote
"Kyle Thering" wrote:

> thanks for the suggestion that did get rid of that error, but now i
> have a new one
>
> Msg 102, Level 15, State 1, Line 4
> Incorrect syntax near ':'.
> Msg 102, Level 15, State 1, Line 10
> Incorrect syntax near ':'.
> and ideas,
>
> Thanks
>
>
> Reg Besseling wrote:
> > Hi Kyle
> >
> > It looks like you have put the getdate() function in quotes changing it into
> > a character value
> >
> > change all your 'GETDATE()'  to getdate()
> >
> > and  CONVERT(varchar, '+@GETDATE+', 112)  to  CONVERT(varchar, @GETDATE, 112)
> >
> > HTH
> > --
> > Regards
> >
> > Reg Besseling
> >
> >
> > "Kyle Thering" wrote:
> >
> > > i am having some problems with a stored procedure that i created its
> > > giving me an
> > > Msg 241, Level 16, State 1, Procedure AS400ImportCSSCT, Line 19
> > > Conversion failed when converting datetime from character string.
> > > error any information would be helpfull, what the SP is sposed to do is
> > > delete all data that is in the current shift and then recopy it from a
> > > linked server shift times are 6AM to 6PM and 6PM to 6AM
> > > Any information would be helpful thanks
> > > [dbo].[SGCGCUPPREP](
> > >     [CP_PDSTAT] [char](1) NOT NULL,
> > >     [CP_PDTYPE] [char](2) NOT NULL,
> > >     [CP_PDSERL] [decimal](9, 0) NOT NULL,
> > >     [CP_ICORDR] [char](7) NOT NULL,
> > >     [CP_ICLINE] [char](4) NOT NULL,
> > >     [CP_PDCUST] [decimal](5, 0) NOT NULL,
> > >     [CP_PDSCHD] [char](5) NOT NULL,
> > >     [CP_PDCTWC] [char](3) NOT NULL,
> > >     [CP_PDSHFT] [char](1) NOT NULL,
> > >     [CP_PDWS] [char](1) NOT NULL,
> > >     [CP_PDQTY] [decimal](5, 0) NOT NULL,
> > >     [CP_PDTDAT] [char](8) NOT NULL,
> > >     [CP_PDTTIM] [char](8) NOT NULL,
> > >     [CP_PDPBY] [char](14) NOT NULL,
> > >     [CP_PDPTO] [decimal](2, 0) NOT NULL,
> > >     [CP_PDRREJ] [char](1) NOT NULL,
> > >     [CP_PDRCDE] [char](4) NOT NULL,
> > >     [CP_PDRESN] [char](50) NOT NULL,
> > >     [CP_PDTHIK] [decimal](3, 1) NOT NULL,
> > >     [CP_PDCTCF] [char](4) NOT NULL,
> > >     [CP_PDBLEG] [decimal](3, 0) NOT NULL,
> > >     [CP_PDBNUM] [decimal](2, 0) NOT NULL,
> > >     [CP_PDBDEN] [decimal](2, 0) NOT NULL,
> > >     [CP_PDLLEG] [decimal](3, 0) NOT NULL,
> > >     [CP_PDLNUM] [decimal](2, 0) NOT NULL,
> > >     [CP_PDLDEN] [decimal](2, 0) NOT NULL,
> > >     [CP_PDWHLC] [char](5) NOT NULL,
> > >     [CP_PDSRCD] [char](3) NOT NULL,
> > >     [CP_PDSERR] [decimal](9, 0) NOT NULL,
> > >     [CP_PDDNAM] [char](10) NOT NULL,
> > >     [CP_PDDNBR] [char](6) NOT NULL,
> > >     [CP_PDDPGM] [char](10) NOT NULL,
> > >     [CP_PDDUSR] [char](10) NOT NULL,
> > >     [CP_PDDUTD] [char](8) NOT NULL,
> > >     [CP_PDDUTM] [char](8) NOT NULL,
> > >     [CP_PDPSTS] [char](1) NOT NULL,
> > >     [CP_PDLTSF] [decimal](5, 2) NOT NULL,
> > >     [CP_PDLTCD] [char](10) NOT NULL,
> > >     [CP_PDLTDS] [char](50) NOT NULL,
> > >     [CP_PDTTSF] [decimal](5, 2) NOT NULL,
> > >     [CP_PDTTCD] [char](10) NOT NULL,
> > >     [CP_PDTTDS] [char](50) NOT NULL,
> > >     [CP_PDCTSF] [decimal](5, 2) NOT NULL,
> > >     [CP_PDCTCD] [char](10) NOT NULL,
> > >     [CP_PDCTDS] [char](50) NOT NULL,
> > >     [CP_PDBTSF] [decimal](5, 2) NOT NULL,
> > >     [CP_PDBTCD] [char](10) NOT NULL,
> > >     [CP_PDBTDS] [char](50) NOT NULL,
> > >     [CP_PDTSQF] [decimal](7, 2) NOT NULL,
> > >     [CP_PDTSQA] [decimal](7, 2) NOT NULL,
> > >     [CP_ODPSQF] [decimal](6, 4) NOT NULL,
> > >     [CP_ODPRCU] [decimal](5, 2) NOT NULL,
> > >     [CP_ODCSQF] [decimal](6, 4) NOT NULL,
> > >     [CP_ODCSTU] [decimal](5, 2) NOT NULL,
> > >     [CP_UPDMSG] [char](50) NOT NULL,
> > >     [CP_UPDNAM] [char](10) NOT NULL,
> > >     [CP_UPDNBR] [char](6) NOT NULL,
> > >     [CP_UPDPGM] [char](10) NOT NULL,
> > >     [CP_UPDUSR] [char](10) NOT NULL,
> > >     [CP_UPDUTD] [char](10) NOT NULL,
> > >     [CP_UPDUTM] [char](10) NOT NULL,
> > >     [CP_RWTHIK] [decimal](3, 1) NOT NULL,
> > >     [CP_RWCTCF] [char](4) NOT NULL,
> > >     [CP_BCSTAT] [char](1) NOT NULL,
> > >     [CP_PRSTAT] [char](1) NOT NULL,
> > >     [CP_UPDATE] [char](8) NOT NULL
> > > ) ON [PRIMARY]
> > >
> > >  [dbo].[AS400ImportCSSCT]
> > > AS
> > > BEGIN
> > >
> > >     SET NOCOUNT ON;
> > >
> > > DECLARE @GETDATE DATETIME
> > > DECLARE @SQL nvarchar(2000)
> > > DECLARE @shift1 nvarchar(20)
> > > DECLARE @shift2 nvarchar(20)
> > >
> > > SET @shift1 = '6:00:00'
> > > SET @shift2 = '18:00:00'
> > >
> > > SET @GETDATE = 'GETDATE()'
> > >
> > > SET @SQL =
> > > CASE
> > >     WHEN DATEPART(HH, @GETDATE) >= 6 AND DATEPART(HH, @GETDATE) < 18 THEN
> > >         'DELETE
> > >         FROM CSSCT.dbo.SGCGCUPPREP
> > >         WHERE
> > >             CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
> > > '+@shift1+' AND
> > >             CP_PDTTIM < '+@shift2+'
> > >         INSERT INTO CSSCT.dbo.SGCGCUPPREP
> > >         SELECT AS400.*
> > >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> > >         WHERE
> > >             CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND CP_PDTTIM >=
> > > '+@shift1+' AND
> > >             CP_PDTTIM < '+@shift2
> > >     WHEN DATEPART(HH, @GETDATE) < 6 THEN
> > >         'DELETE
> > >         FROM CSSCT.dbo.SGCGCUPPREP
> > >         WHERE
> > >             CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+', 112) AND
> > >             CP_PDTTIM >= '+@shift2+'
> > >         INSERT INTO CSSCT.dbo.SGCGCUPPREP
> > >         SELECT AS400.*
> > >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> > >         WHERE
> > >             CP_PDTDAT = CONVERT(varchar, DATEADD(d, -1, '+@GETDATE+'), 112) AND
> > >             CP_PDTTIM >= '+@shift2
> > >     WHEN DATEPART(HH, @GETDATE) >= 18 THEN
> > >         N'DELETE
> > >         FROM CSSCT.dbo.SGCGCUPPREP
> > >         WHERE
> > >            CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
> > >            CP_PDTTIM >= '+@shift2+'
> > >         SELECT AS400.*
> > >         FROM SGCGAS400.S65E035D.SPCLSGCG.CUPPREP AS400
> > >         WHERE
> > >            CP_PDTDAT = CONVERT(varchar, '+@GETDATE+', 112) AND
> > >            CP_PDTTIM >= '+@shift2
> > > END
> > >
> > > Exec sp_executesql @SQL
> > >
> > > END
> > >
> > >
>
>

AddThis Social Bookmark Button

Post Other interesting topics
random select a row based a weight column.
store results over network
dynamic SQL versus a huge IF structure
Variable table name in a cursor declaration
Comparing two tables