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 ?
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >>
>> >>
>> >>.
>> >>
>> >>.
>> >>
>> >.
>> >
>
>.
>