Linekd Server and DateTime Problem

,
I runs this querys from QA in one session to my local
server
// tb1 in local server
Select * from TB1
where cdatetime <= '30/01/2003'
runs ok
// tb1 in linked server
Select * from LinkedServer.DB.dbo.TB1
where cdatetime <= '30/01/2003'
fails with
conversion from data char to l DateTime out of Range
// tb1 in linked server
Select * from LinkedServer.DB.dbo.TB1
where cdatetime <= '01/30/2003'
fails with
conversion from data char to l DateTime out of Range
Two servers have the same language and dateformat dmy
If I open one connection in QA to the linked server, the
query
Select * from LinkedServer.DB.dbo.TB1
where cdatetime <= '30/01/2003'
runs ok.
I've tested with many diferents formats mmddyyyy,
yyyymmdd, and so on.
Any Ideas ?amosquera
Try use 'yyyymmdd' Format
"amosquera" <alvarosusana@.vodafone.es> wrote in message
news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
> ,
> I runs this querys from QA in one session to my local
> server
> // tb1 in local server
> Select * from TB1
> where cdatetime <= '30/01/2003'
> runs ok
> // tb1 in linked server
> Select * from LinkedServer.DB.dbo.TB1
> where cdatetime <= '30/01/2003'
> fails with
> conversion from data char to l DateTime out of Range
> // tb1 in linked server
> Select * from LinkedServer.DB.dbo.TB1
> where cdatetime <= '01/30/2003'
> fails with
> conversion from data char to l DateTime out of Range
>
> Two servers have the same language and dateformat dmy
> If I open one connection in QA to the linked server, the
> query
> Select * from LinkedServer.DB.dbo.TB1
> where cdatetime <= '30/01/2003'
> runs ok.
> I've tested with many diferents formats mmddyyyy,
> yyyymmdd, and so on.
> Any Ideas ?
>|||In the original message I've said
I've tested with many diferents formats mmddyyyy,yyyymmdd,
and so on.
and the query fails.
>--Original Message--
>amosquera
>Try use 'yyyymmdd' Format
>
>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> ,
>> I runs this querys from QA in one session to my local
>> server
>> // tb1 in local server
>> Select * from TB1
>> where cdatetime <= '30/01/2003'
>> runs ok
>> // tb1 in linked server
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '30/01/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>> // tb1 in linked server
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '01/30/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>>
>> Two servers have the same language and dateformat dmy
>> If I open one connection in QA to the linked server, the
>> query
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '30/01/2003'
>> runs ok.
>> I've tested with many diferents formats mmddyyyy,
>> yyyymmdd, and so on.
>> Any Ideas ?
>
>.
>|||Select * from LinkedServer.DB.dbo.TB1
> where cdatetime <= '01/30/2003'
> fails with
> conversion from data char to l DateTime out of Range
>
This one fails because SQL Server cannot recognize 30 as a month .
Have you changed it to '30/01/2003'?
One more thought: Have you checked regimal settings on the linked server?
I mean an year 2003 or 03 .
"amosquera" <alvarosusana@.vodafone.es> wrote in message
news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
> In the original message I've said
> I've tested with many diferents formats mmddyyyy,yyyymmdd,
> and so on.
> and the query fails.
> >--Original Message--
> >amosquera
> >Try use 'yyyymmdd' Format
> >
> >
> >
> >"amosquera" <alvarosusana@.vodafone.es> wrote in message
> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
> >> ,
> >>
> >> I runs this querys from QA in one session to my local
> >> server
> >>
> >> // tb1 in local server
> >> Select * from TB1
> >> where cdatetime <= '30/01/2003'
> >>
> >> runs ok
> >>
> >> // tb1 in linked server
> >>
> >> Select * from LinkedServer.DB.dbo.TB1
> >> where cdatetime <= '30/01/2003'
> >>
> >> fails with
> >> conversion from data char to l DateTime out of Range
> >>
> >> // tb1 in linked server
> >>
> >> Select * from LinkedServer.DB.dbo.TB1
> >> where cdatetime <= '01/30/2003'
> >>
> >> fails with
> >> conversion from data char to l DateTime out of Range
> >>
> >>
> >> Two servers have the same language and dateformat dmy
> >>
> >> If I open one connection in QA to the linked server, the
> >> query
> >>
> >> Select * from LinkedServer.DB.dbo.TB1
> >> where cdatetime <= '30/01/2003'
> >>
> >> runs ok.
> >>
> >> I've tested with many diferents formats mmddyyyy,
> >> yyyymmdd, and so on.
> >>
> >> Any Ideas ?
> >>
> >
> >
> >.
> >|||Hi,
Well I think that I've tested all possible formats of
datetime and the query fails.
>--Original Message--
> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '01/30/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>This one fails because SQL Server cannot recognize 30 as
a month .
>Have you changed it to '30/01/2003'?
>One more thought: Have you checked regimal settings on
the linked server?
>I mean an year 2003 or 03 .
>
>
>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
>> In the original message I've said
>> I've tested with many diferents formats
mmddyyyy,yyyymmdd,
>> and so on.
>> and the query fails.
>> >--Original Message--
>> >amosquera
>> >Try use 'yyyymmdd' Format
>> >
>> >
>> >
>> >"amosquera" <alvarosusana@.vodafone.es> wrote in message
>> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> >> ,
>> >>
>> >> I runs this querys from QA in one session to my local
>> >> server
>> >>
>> >> // tb1 in local server
>> >> Select * from TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of Range
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '01/30/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of Range
>> >>
>> >>
>> >> Two servers have the same language and dateformat dmy
>> >>
>> >> If I open one connection in QA to the linked server,
the
>> >> query
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok.
>> >>
>> >> I've tested with many diferents formats mmddyyyy,
>> >> yyyymmdd, and so on.
>> >>
>> >> Any Ideas ?
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||The linked server has the same settings options that the
main server.
dd/mm/yyyy
>--Original Message--
>Hi,
>Well I think that I've tested all possible formats of
>datetime and the query fails.
>>--Original Message--
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '01/30/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>>This one fails because SQL Server cannot recognize 30 as
>a month .
>>Have you changed it to '30/01/2003'?
>>One more thought: Have you checked regimal settings on
>the linked server?
>>I mean an year 2003 or 03 .
>>
>>
>>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>>news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
>> In the original message I've said
>> I've tested with many diferents formats
>mmddyyyy,yyyymmdd,
>> and so on.
>> and the query fails.
>> >--Original Message--
>> >amosquera
>> >Try use 'yyyymmdd' Format
>> >
>> >
>> >
>> >"amosquera" <alvarosusana@.vodafone.es> wrote in
message
>> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> >> ,
>> >>
>> >> I runs this querys from QA in one session to my
local
>> >> server
>> >>
>> >> // tb1 in local server
>> >> Select * from TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of Range
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '01/30/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of Range
>> >>
>> >>
>> >> Two servers have the same language and dateformat
dmy
>> >>
>> >> If I open one connection in QA to the linked
server,
>the
>> >> query
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok.
>> >>
>> >> I've tested with many diferents formats mmddyyyy,
>> >> yyyymmdd, and so on.
>> >>
>> >> Any Ideas ?
>> >>
>> >
>> >
>> >.
>> >
>>
>>.
>.
>|||I've had a new test:
Select * from LinkedServer.DB.dbo.TB1
where convert(char(10),cdatetime,103) <= '01/30/2003'
and it runs ok but the execution plan show that the query
uses a table scan rather than a index seek on cdatetime
column.
Thanks.
>--Original Message--
>The linked server has the same settings options that the
>main server.
>dd/mm/yyyy
>>--Original Message--
>>Hi,
>>Well I think that I've tested all possible formats of
>>datetime and the query fails.
>>--Original Message--
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '01/30/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>>This one fails because SQL Server cannot recognize 30
as
>>a month .
>>Have you changed it to '30/01/2003'?
>>One more thought: Have you checked regimal settings on
>>the linked server?
>>I mean an year 2003 or 03 .
>>
>>
>>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>>news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
>> In the original message I've said
>> I've tested with many diferents formats
>>mmddyyyy,yyyymmdd,
>> and so on.
>> and the query fails.
>> >--Original Message--
>> >amosquera
>> >Try use 'yyyymmdd' Format
>> >
>> >
>> >
>> >"amosquera" <alvarosusana@.vodafone.es> wrote in
>message
>> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> >> ,
>> >>
>> >> I runs this querys from QA in one session to my
>local
>> >> server
>> >>
>> >> // tb1 in local server
>> >> Select * from TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of
Range
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '01/30/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of
Range
>> >>
>> >>
>> >> Two servers have the same language and dateformat
>dmy
>> >>
>> >> If I open one connection in QA to the linked
>server,
>>the
>> >> query
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok.
>> >>
>> >> I've tested with many diferents formats mmddyyyy,
>> >> yyyymmdd, and so on.
>> >>
>> >> Any Ideas ?
>> >>
>> >
>> >
>> >.
>> >
>>
>>.
>>.
>.
>|||Hi Uri,
Thanks in advantaje
I know this, only to remark that although the query runs,
the execution plan is not valid for me.
Any more ideas about the original question ?
>--Original Message--
>The CONVERT dictate to optimyzer use a INDEX SCAN
insetad of INDEX SEEK
>
>
>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>news:0a4d01c36bb3$36968ce0$a401280a@.phx.gbl...
>> I've had a new test:
>> Select * from LinkedServer.DB.dbo.TB1
>> where convert(char(10),cdatetime,103) <= '01/30/2003'
>> and it runs ok but the execution plan show that the
query
>> uses a table scan rather than a index seek on cdatetime
>> column.
>> Thanks.
>>
>> >--Original Message--
>> >The linked server has the same settings options that
the
>> >main server.
>> >dd/mm/yyyy
>> >>--Original Message--
>> >>Hi,
>> >>
>> >>Well I think that I've tested all possible formats of
>> >>datetime and the query fails.
>> >>
>> >>--Original Message--
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '01/30/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of
Range
>> >>
>> >>This one fails because SQL Server cannot recognize 30
>> as
>> >>a month .
>> >>Have you changed it to '30/01/2003'?
>> >>One more thought: Have you checked regimal settings
on
>> >>the linked server?
>> >>I mean an year 2003 or 03 .
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>"amosquera" <alvarosusana@.vodafone.es> wrote in
message
>> >>news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
>> >> In the original message I've said
>> >>
>> >> I've tested with many diferents formats
>> >>mmddyyyy,yyyymmdd,
>> >> and so on.
>> >>
>> >> and the query fails.
>> >>
>> >> >--Original Message--
>> >> >amosquera
>> >> >Try use 'yyyymmdd' Format
>> >> >
>> >> >
>> >> >
>> >> >"amosquera" <alvarosusana@.vodafone.es> wrote in
>> >message
>> >> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> >> >> ,
>> >> >>
>> >> >> I runs this querys from QA in one session to my
>> >local
>> >> >> server
>> >> >>
>> >> >> // tb1 in local server
>> >> >> Select * from TB1
>> >> >> where cdatetime <= '30/01/2003'
>> >> >>
>> >> >> runs ok
>> >> >>
>> >> >> // tb1 in linked server
>> >> >>
>> >> >> Select * from LinkedServer.DB.dbo.TB1
>> >> >> where cdatetime <= '30/01/2003'
>> >> >>
>> >> >> fails with
>> >> >> conversion from data char to l DateTime out of
>> Range
>> >> >>
>> >> >> // tb1 in linked server
>> >> >>
>> >> >> Select * from LinkedServer.DB.dbo.TB1
>> >> >> where cdatetime <= '01/30/2003'
>> >> >>
>> >> >> fails with
>> >> >> conversion from data char to l DateTime out of
>> Range
>> >> >>
>> >> >>
>> >> >> Two servers have the same language and
dateformat
>> >dmy
>> >> >>
>> >> >> If I open one connection in QA to the linked
>> >server,
>> >>the
>> >> >> query
>> >> >>
>> >> >> Select * from LinkedServer.DB.dbo.TB1
>> >> >> where cdatetime <= '30/01/2003'
>> >> >>
>> >> >> runs ok.
>> >> >>
>> >> >> I've tested with many diferents formats
mmddyyyy,
>> >> >> yyyymmdd, and so on.
>> >> >>
>> >> >> Any Ideas ?
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >>
>> >>
>> >>.
>> >>
>> >>.
>> >>
>> >.
>> >
>
>.
>

Line 1: Incorrect syntax near getlistofspclass.

what am i doing wrong.. :(

CREATE PROCEDURE getlistofspclass
(
@.date datetime,
@.s varchar(8000) output
)

AS

select @.s = coalesce(@.s + ' & ', '') + '"' + convert(varchar(10),dbo.calader.timestart)+ ' ' + dbo.classT.classname + '<p>'
FROM dbo.calader INNER JOIN dbo.classT ON dbo.calader.classid = dbo.classT.classcode
WHERE dbo.calader.caledardatedates = @.date
exec( @.s)
GO

The procedure's parameters should not be enclosed in parentheses. Remove the parentheses that surround the parameters and you should have better luck.|||

my god!! i spent 4 hours on that and $60 on a Que sql server programming book. :(

but now it works. i can hang myself now

|||

hello can you help me again. ...my asp.net code is not working.. it will not send the date to the pramater.

can you make a sameple code in asp.net vb of how you will send a date to that stored procuder and how you would get the returned value

please. thank you verymuch

|||

All I have is a C# version I made the other day, sorry. But it's close enough to VB you should be able to figure out how to port it.

1public int InsertNewThread(Thread ThreadToInsert,out int InternalID)2{3using(SqlConnection sqlConnection =new SqlConnection(GetConnectionString()))4{5SqlCommand sqlCommand =new SqlCommand("Thread_Insert", sqlConnection);6sqlCommand.CommandType = CommandType.StoredProcedure;7sqlCommand.Parameters.Add("@.threadSummary", SqlDbType.NVarChar, 75).Value = ThreadToInsert.ThreadSummary;8sqlCommand.Parameters.Add("@.boardID", SqlDbType.Int).Value = ThreadToInsert.BoardID;9sqlCommand.Parameters.Add("@.internalID", SqlDbType.Int).Direction = ParameterDirection.Output;10sqlConnection.Open();11int RowsEffected = sqlCommand.ExecuteNonQuery();12sqlConnection.Close();13InternalID = (int) sqlCommand.Parameters["@.internalID"].Value;14return RowsEffected;15}16}

12/****** Object: Stored Procedure dbo.Thread_Insert Script Date: 7/28/2006 7:24:19 PM ******/3CREATE PROCEDURE Thread_Insert4@.threadSummaryNVARCHAR( 75 ),5@.internalIDINT OUTPUT6AS7INSERT INTO8 threads( threadSummary )910VALUES11 ( @.threadSummary )1213SELECT14@.internalID = SCOPE_IDENTITY ( )1516GO

|||

thanks again. you know the speed of getting the return value was just the same as putting the sql statement on the webpage itself...people say storedprocedures are faster..oh well

thanks everything worked

|||

Stored procedures are faster. If you coded one page without stored procedures, and another with stored procedures, and timed them with your watch, then you won't notice much.

Take a web server (or multiple web servers) using complex queries and time the difference when they are getting hit a few hundred requests per second, and you will notice a difference, especially in older versions of SQL Server. The newer ones have added a few little things that reduce the difference dramatically in most cases (Cached query plans, even for dynamic queries). They basically turn even dynamic queries into a kind of "temporary" stored procedure, getting most of the sp's benefits, a very nice addition to SQL Server.