Problems with T-SQL in SQL Job

I have a strange problem. I have some T-SQL that functions properly in
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.You should be able to use the convert function directly:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
<stephan@.pathcom.com> wrote in message
news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
I have a strange problem. I have some T-SQL that functions properly in
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.|||Thanks for the advice Tom but I still receive the same error when it
tries to convert the data. Like I said, this works well in Analyzer,
but not when it's set up as a step in a Job.
SR
Tom Moreau wrote:
> You should be able to use the convert function directly:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> <stephan@.pathcom.com> wrote in message
> news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
> I have a strange problem. I have some T-SQL that functions properly
in
> Analyzer but fails when I write the same code into a SQL Job:
> DECLARE @.DateTime datetime
> DECLARE @.DateNoTime varchar(10)
> SET @.DateTime = (GetDate())
> SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
'/'
> + rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> DECLARE @.Result datetime
> SET @.Result = CONVERT(datetime, @.DateNoTime)
> The last line, the CONVERT, fails in the SQL Job. I've tried using
> CAST to see if that made a difference but I received the same error.
> Here is the error SQL Agent generates:
> The conversion of a char data type to a datetime data type resulted
in
> an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> step failed.
> Does anyone have any thoughts? I'm running SQL 2000.|||Tom's advice should've worked. Your convert statement
is faulty.
What version of SQL Server are you running?
<stephan@.pathcom.com> wrote in message
news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
> Thanks for the advice Tom but I still receive the same error when it
> tries to convert the data. Like I said, this works well in Analyzer,
> but not when it's set up as a step in a Job.
> SR
> Tom Moreau wrote:
> > You should be able to use the convert function directly:
> >
> > SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > <stephan@.pathcom.com> wrote in message
> > news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
> > I have a strange problem. I have some T-SQL that functions properly
> in
> > Analyzer but fails when I write the same code into a SQL Job:
> >
> > DECLARE @.DateTime datetime
> > DECLARE @.DateNoTime varchar(10)
> >
> > SET @.DateTime = (GetDate())
> > SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
> '/'
> > + rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> > rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> >
> > DECLARE @.Result datetime
> > SET @.Result = CONVERT(datetime, @.DateNoTime)
> >
> > The last line, the CONVERT, fails in the SQL Job. I've tried using
> > CAST to see if that made a difference but I received the same error.
> > Here is the error SQL Agent generates:
> >
> > The conversion of a char data type to a datetime data type resulted
> in
> > an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> > Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> > step failed.
> >
> > Does anyone have any thoughts? I'm running SQL 2000.
>|||Stephan,
Try replacing the final statement with
SET @.Result = CONVERT(datetime, @.DateNoTime,103)
For whatever reason, the context in which this runs as a job step
has different language or dateformat settings than your Query Analyzer,
and you are applying CONVERT without specifying the format of
the date string, hence leaving the interpretation dependent on the context.
By the way, you can remove the time from a date without anything
depending on string formats for datetime:
SET @.Result = DATEADD(d,DATEDIFF(d,0,@.DateTime),0)
Steve Kass
Drew University
stephan@.pathcom.com wrote:
>I have a strange problem. I have some T-SQL that functions properly in
>Analyzer but fails when I write the same code into a SQL Job:
>DECLARE @.DateTime datetime
>DECLARE @.DateNoTime varchar(10)
>SET @.DateTime = (GetDate())
>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
>DECLARE @.Result datetime
>SET @.Result = CONVERT(datetime, @.DateNoTime)
>The last line, the CONVERT, fails in the SQL Job. I've tried using
>CAST to see if that made a difference but I received the same error.
>Here is the error SQL Agent generates:
>The conversion of a char data type to a datetime data type resulted in
>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
>step failed.
>Does anyone have any thoughts? I'm running SQL 2000.
>
>|||Armando,
Tom's statement didn't work because it did not specify a format for
the conversion back to datetime. Tom's statement would have worked this
way:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
SK
Armando Prato wrote:
>Tom's advice should've worked. Your convert statement
>is faulty.
>What version of SQL Server are you running?
><stephan@.pathcom.com> wrote in message
>news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
>
>>Thanks for the advice Tom but I still receive the same error when it
>>tries to convert the data. Like I said, this works well in Analyzer,
>>but not when it's set up as a step in a Job.
>>SR
>>Tom Moreau wrote:
>>
>>You should be able to use the convert function directly:
>>SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
>>--
>>Tom
>>---
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com
>>
>><stephan@.pathcom.com> wrote in message
>>news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
>>I have a strange problem. I have some T-SQL that functions properly
>>
>>in
>>
>>Analyzer but fails when I write the same code into a SQL Job:
>>DECLARE @.DateTime datetime
>>DECLARE @.DateNoTime varchar(10)
>>SET @.DateTime = (GetDate())
>>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
>>
>>'/'
>>
>>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
>>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
>>DECLARE @.Result datetime
>>SET @.Result = CONVERT(datetime, @.DateNoTime)
>>The last line, the CONVERT, fails in the SQL Job. I've tried using
>>CAST to see if that made a difference but I received the same error.
>>Here is the error SQL Agent generates:
>>The conversion of a char data type to a datetime data type resulted
>>
>>in
>>
>>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
>>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
>>step failed.
>>Does anyone have any thoughts? I'm running SQL 2000.
>>
>
>|||Ah yes, I see.
Thanks.
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23mhjopo7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Armando,
> Tom's statement didn't work because it did not specify a format for
> the conversion back to datetime. Tom's statement would have worked this
> way:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
> SK
>
> Armando Prato wrote:
> >Tom's advice should've worked. Your convert statement
> >is faulty.
> >
> >What version of SQL Server are you running?
> >
> ><stephan@.pathcom.com> wrote in message
> >news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
> >
> >
> >>Thanks for the advice Tom but I still receive the same error when it
> >>tries to convert the data. Like I said, this works well in Analyzer,
> >>but not when it's set up as a step in a Job.
> >>
> >>SR
> >>
> >>Tom Moreau wrote:
> >>
> >>
> >>You should be able to use the convert function directly:
> >>
> >>SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> >>
> >>--
> >>Tom
> >>
> >>---
> >>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >>SQL Server MVP
> >>Columnist, SQL Server Professional
> >>Toronto, ON Canada
> >>www.pinnaclepublishing.com
> >>
> >>
> >><stephan@.pathcom.com> wrote in message
> >>news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
> >>I have a strange problem. I have some T-SQL that functions properly
> >>
> >>
> >>in
> >>
> >>
> >>Analyzer but fails when I write the same code into a SQL Job:
> >>
> >>DECLARE @.DateTime datetime
> >>DECLARE @.DateNoTime varchar(10)
> >>
> >>SET @.DateTime = (GetDate())
> >>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
> >>
> >>
> >>'/'
> >>
> >>
> >>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> >>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> >>
> >>DECLARE @.Result datetime
> >>SET @.Result = CONVERT(datetime, @.DateNoTime)
> >>
> >>The last line, the CONVERT, fails in the SQL Job. I've tried using
> >>CAST to see if that made a difference but I received the same error.
> >>Here is the error SQL Agent generates:
> >>
> >>The conversion of a char data type to a datetime data type resulted
> >>
> >>
> >>in
> >>
> >>
> >>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> >>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> >>step failed.
> >>
> >>Does anyone have any thoughts? I'm running SQL 2000.
> >>
> >>
> >
> >
> >
> >|||Thanks guys. That worked like a charm.
Thanks Steve for the explanation why it didn't work. Strange that the
Agent and the Analyzer use a different engine...
SR|||Stephan,
you may find that the owner of the sql agent job(and/or the sqlserver agent
account) uses a different account to the one you have used to access QA and
as a result possibly has a different default language setting which has
resulted in a different date format.
HTH
<stephan@.pathcom.com> wrote in message
news:1104433299.670932.135410@.c13g2000cwb.googlegroups.com...
> Thanks guys. That worked like a charm.
> Thanks Steve for the explanation why it didn't work. Strange that the
> Agent and the Analyzer use a different engine...
> SR
>