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:
> in
> '/'
> in
>|||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...
>
>
>|||Ah yes, I see.
Thanks.
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23mhjopo7EHA.1408@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> 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:
>|||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
>sql

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.googlegro ups.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.googlegro ups.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.googlegro ups.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:
> in
> '/'
> in
>
|||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.googlegr oups.com...
>
>
>
|||Ah yes, I see.
Thanks.
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23mhjopo7EHA.1408@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> 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:
|||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.googlegr oups.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
>

Problems with mimimum value for DateTime Package Variable since SP1

Since I've installed SP1 the minimum value for a package variable has changed.

The former version used 12/30/1899 as the minimum value that can be entered.
Now the min value has changed to something about 1960.

During package execution I'd like to distinguish between two states:

1. The Variable has not yet been written to.
2. Any of my executable has written to it.

At the moment I check the value of it to be the minimum value (see above) or not.
So if I develop packages under SP1 I get into trouble.

Any way out of it?
Thanks
Fridtjof

[EDIT] The mindate in C# differs from both date values mentioned above! Consistency? Hum![/EDIT]

How did you determine that you can't put values below 1960 in there? I (or more accurately Darren who is sat right next to me) has just typed the value 01/01/1900 into the variables window and it sems to take it OK.

What specifically are you not able to do?

-Jamie

|||

Typing works fine, and the following code worked too.

Public Sub Main()

Dts.Variables("Variable").Value = DateTime.MaxValue
System.Windows.Forms.MessageBox.Show(Dts.Variables("Variable").Value.ToString())

Dts.Variables("Variable").Value = DateTime.MinValue
System.Windows.Forms.MessageBox.Show(Dts.Variables("Variable").Value.ToString())

Dts.TaskResult = Dts.Results.Success

End Sub

|||Oops, I don't really know what went wrong but it works as you mentioned.

Sorry for posting too quick...

Problems with DateTime

Hello all,
I have a problem when I try to insert with a SQL Command a value obtained from a Calendar Control in a SQL Server table. I get the date from the calendar and I put it in a TextBox, but when I try to insert this value in a DateTime defined field I have the following error:
The string or binary data would be truncated. The instruction was finished.

Could you help me?

Thanks.You need to convert the text into a c#/vb date type first. You're prob' hitting a localization issue with the date format.|||I'm certainly no SQL server pro but from my limited experience, SQL server is persnickety about datetime values. I avoid the problem altogether by passing the date in your SQL statement as a string (wrapping it w/in single quotes) or, if you are using sprocs, converting it to datetime w/in the sproc in SQL Server.

good luck.|||No don't wrap it as a string that's exactly where you start getting the problems. If you wrap it as a string you need to either know the database format (and be able to convert to it) or you have to convert to a universal format. Much better to use a param.

Problems with date

Hi, my name is MANUEL. I don't speak english very well...
Today I am working with SQLServer. I would want to compare two dateTime fields for to determine if two or more customers are connected in the same moment into my applications...
For example:
Begin date of connections:
16/01/2007 16.19.59
end date of connection
16/01/2007 16.25.23 for customers 01

Begin date of connections:
16/01/2007 16.20.59
end date of connection
16/01/2007 16.24.23 for customers 02

What i have to do!!!!
I must make this control to every hour of the day
Please Help!

Hi Manuel,

If I understand you question, you are looking to write a query that shows overlapping sessions.

If you start with a list of all the distinct start times, you can then use a correlated query to look for overlapping sessions (something like):

Code Snippet

select s1.app, st, (
select count(*)
from Sessions as s2
where
s1.app = s2.app

and s1.st >= s2.StartDateTime
and s1.st < s2.StopDateTime ) as Overlaps
from (
select distinct app, StartDateTime st
from Sessions
) as s1

Note that set based solutions around this type of problem can become really slow, really quickly! A cursor based solution can scale much better. You'd have to do some testing to see which would work best for you.

Hope that helps.

Jamie

|||

Here is a query that will retrieve all the connections that are overlapping.

Code Snippet

Select Conn1.Customer, Conn1.BeginDate, Conn1.EndDate,

Conn2.Customer, Conn2.BeginDate, Conn2.EndDate, *

From Connections Conn1, Connections Conn2

Where Conn2.BeginDate Between Conn1.BeginDate And Conn1.EndDate

And Conn1.ID <> Conn2.ID

I'm assuming the table name is called 'Connections' and that there is a primary key which is called ID. Here I've joined the table on itself looking for connections that have their start time between another connection's start and end time. I added 'Conn1.ID <> Conn2.ID' so that the query does not assume that the same connection is as an overlapping one.

I hope this answers your question.

Best regards,

Sami Samir

|||

This query might help you...

Code Snippet

Select
MainThread.CustomerId Customer
,MainThread.Startdate Customer1StartAt
,MainThread.Enddate Customer2EndAt
,ParlelThread.CustomerId ParllelWith
,ParlelThread.Startdate ParllelCustomerJoinAt
,ParlelThread.Enddate ParllelCustomerLeaveAt
,DateDiff(Mi, ParlelThread.Startdate, Case When MainThread.Enddate < ParlelThread.Enddate Then MainThread.Enddate Else ParlelThread.Enddate End ) TotalMinutesParllel
from AuditLog MainThread
Join AuditLog ParlelThread On
MainThread.Enddate > ParlelThread.Startdate And
MainThread.Startdate <= ParlelThread.Startdate
And MainThread.CustomerId <> ParlelThread.CustomerId
Order By 1

|||Sorry!I don't know SQL SERVER too

I have only one table which contains:
IDCustomers
Name
BeginDate
EndDate

My really problem is to abtain the list of cutomers whitch day,mount, years is the same and

have the same hour of logon.

I ask for new excuse for my English

|||

Try this :

Substitute your actual table name in <YourTable>

select BeginDate,IDCustomers,Name,EndDate

from <YourTable>

where BeginDate in

(

select BeginDate

from <YourTable>

group by BeginDate

having count(*) > 1

)

order by BeginDate,IDCustomers

Thanks.

Naras.

|||

The following query may help you...

Code Snippet

Select
MainThread.CustomerId Customer
,MainThread.Startdate Customer1StartAt
,MainThread.Enddate Customer2EndAt
,ParlelThread.CustomerId ParllelWith
,ParlelThread.Startdate ParllelCustomerJoinAt
,ParlelThread.Enddate ParllelCustomerLeaveAt
Into #Data
from <YourTable> MainThread
Join <YourTable>ParlelThread On
MainThread.Enddate > ParlelThread.Startdate And
MainThread.Startdate <= ParlelThread.Startdate
And MainThread.CustomerId <> ParlelThread.CustomerId
Order By 1

Select Customer, Customer1StartAt, Customer2EndAt From #Data
Union
Select ParllelWith, ParllelCustomerJoinAt, ParllelCustomerLeaveAt From #Data

Drop Table #Data

|||thanks to all… I have resolved the problem with one function.
you have been fast and kind
BYE