Error converting datetime in SQL Server job 1

[flstffatboy]

Hello,

I'm getting the following error when running this code within a SQL Server 2000 job. There code runs fine within query analyzer. Could anyone shed any light on this?

Error - "Syntax error converting datetime from character string. [SQLSTATE 22007] (Error 241). The step failed."

Code below:
declare @PLine as Varchar(3)
declare @BegDt as datetime
declare @EndDt as datetime

set @PLine = '230'
set @BegDt = (select "Period Start Date Month"
from cognos.dbo.Fydates2
where datediff(day, "Date",getdate()) = 1)
set @EndDt = getdate()

"Period Start Date Month" and "Date" fields are both a smalldatetime

Thanks,
FLSTF

 

[bborissov]

Try:

select @BegDt = [Period Start Date Month]
       from cognos.dbo.Fydates2
       where datediff(day, [Date], getdate()) = 1)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[foxdev]

What Borislav said. The reason it may run fine in Query Analyzer and not in your job is they differ in how their "set quoted identifier" setting is configured. When in doubt, you can always explicitly set it, but I think using the brackets is the best all-around approach.

--------------

http://www.foxdev.com

 

[flstffatboy]

bborissov,

That worked perfectly.

Thank you very much.

FLSTF

 

[bborissov]

Also messing SET with SELECT is not the best practice anyway

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP