Dates and Server Path - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dates and Server Path

Author

Topic

roy512
Starting Member

12 Posts

Posted - 2005-09-23 : 10:00:48

Hi ti everyone, recently I begin to work with SQL Server 2000, and i have a problem with the next update:

UPDATE [192.168.254.1].DB1.DBO.REGULARIZACION SET FECHA = '12/22/2005' WHERE ID_REGULARIZACION = 25

I get the next error:

"La conversión del tipo de datos char a datetime produjo un valor datetime fuera de intervalo."
(hope you speak spanish

)

when I delete the server's path, the update works fine, maybe sound weird, but with the same query above with a date like this '12/12/2005', works!!, as if it takes the day and month like months, 'cause numbers below or equal than 12 works.

I need to use the server path because I use 4 diferent DBs in 4 diferent servers, and the data type must bu DATETIME. Hope anyone knows how to solve this

(sorry about the english

)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-23 : 10:03:03

Try '2005-12-22'

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 10:04:02

You need to use ISO format yyyymmdd

Try this

UPDATE [192.168.254.1].DB1.DBO.REGULARIZACION SET FECHA = '20051222' WHERE ID_REGULARIZACION = 25

Madhivanan

Failing to plan is Planning to fail

roy512
Starting Member

12 Posts

Posted - 2005-09-23 : 10:06:42

I have tried a lot of formats:

'22-12-2005'
'12-22-2005'
'2005-12-22'
'2005/22/12'
'2005/12/22'
...

etc, etc

roy512
Starting Member

12 Posts

Posted - 2005-09-23 : 10:07:57

Sorry madhivanan, but it didnt work either

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 10:11:22

What does this mean?

La conversión del tipo de datos char a datetime produjo un valor datetime fuera de intervalo."

Madhivanan

Failing to plan is Planning to fail

roy512
Starting Member

12 Posts

Posted - 2005-09-23 : 10:12:13

I can use the server path in SELECTs and INSERTs, the only problem is with an UPDATE, and only with DATEs

roy512
Starting Member

12 Posts

Posted - 2005-09-23 : 10:15:47

Sorry, I'll try to translate the error

"the data type conversion from char to datetime produced a datetime valor out of range"

something like that

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 10:16:11

What is the data type of that Date Column and what is the error you get?

Madhivanan

Failing to plan is Planning to fail

roy512
Starting Member

12 Posts

Posted - 2005-09-23 : 10:21:09

Im using a DATETIME data type for that field,and the error is the one I write before, exactly a dont know why, 'cause it seems tha the problem is the servers path, 'cause if a dont put it, the query works fine.

I tried to use CONVERT and CAST, but I get the same error

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 10:32:35

What do you get if you run this?

Select top 10 * from [192.168.254.1].DB1.DBO.REGULARIZACION WHERE ID_REGULARIZACION = 25

Madhivanan

Failing to plan is Planning to fail

roy512
Starting Member

12 Posts

Posted - 2005-09-23 : 10:40:54

That query works fine, because of the WHERE, it only brings me 1 result, but works

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 10:45:35

Then The update query should work
Take this example


declare @t table(i int, d datetime)
insert into @t(i) values(1)
select * from @t
Update @t set d='20051222' where i=1
select * from @t

Madhivanan

Failing to plan is Planning to fail

roy512
Starting Member

12 Posts

Posted - 2005-09-23 : 11:00:37

Well, it work fine, I dont see where you refer to the server path, but I guess that I can do a Stored Procedure in each Database to do the Updates, and it should work to, im only guessing, but your query gave me the idea, you think it could work?, o could you explain me your example

Kristen
Test

22859 Posts

Posted - 2005-09-23 : 11:24:40

SET DATEFORMAT YMD
UPDATE [192.168.254.1].DB1.DBO.REGULARIZACION SET FECHA = '20051222' WHERE ID_REGULARIZACION = 25

or perhaps

SET DATEFORMAT YMD
UPDATE [192.168.254.1].DB1.DBO.REGULARIZACION SET FECHA = CONVERT(datetime, '20051222') WHERE ID_REGULARIZACION = 25

Dunno if this will help. The DATEFORMAT thing is Belt and Braces. You should be fine with an ISO date (YYYYMMDD), but obviously that didn't work for you.

Kristen

roy512
Starting Member

12 Posts

Posted - 2005-09-23 : 11:39:57

it didnt work Kirsten

, but thanks anyway, did anyone have a chance to try, you dont need to have diferent servers, only write the complete path of your machine with an IP adress, cause if you use the PC name instead of the IP the query works fine (only if local).

And try to do an UPDATE with a date, send me the query, and if it works ...., well, may be the problem is the layer 8 of the OSI model

roy512
Starting Member

12 Posts

Posted - 2005-09-23 : 12:34:21

mmmm, I recently noticed thah every query using dates and the server path doesn't work

, this will be baaaddd

Kristen
Test

22859 Posts

Posted - 2005-09-23 : 14:17:40

Da Books say dat using da ISO date is Da Bizness!

So I dunno why your using yyyymmdd format isn't working. But it Jolly Well Should Do!

I'm having a struggle to see why IP addressing the server would make a difference.

I can believe that another remote server could be set up with a different Locale etc.

EXEC sp_linkedservers

does actually show "192.168.254.1" as a valid, configured, linked server does it?

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-23 : 14:32:34

Is the linked server a MS SQL Server or some other type of database, like Oracle, FoxPro, etc.?

That would make a big difference.

CODO ERGO SUM

roy512
Starting Member

12 Posts

Posted - 2005-09-26 : 09:59:56

The linked server its also SQL Server, and in the linkedservers the IP address its active, a tried doing this and this works fine, I hope there is an easier way to do it.

declare @a datetime
set @a = '20051222'
update [192.168.2.1].db1.dbo,regularizacion set fecha = @a where ....

roy512
Starting Member

12 Posts

Posted - 2005-09-26 : 13:20:55

Here's a screenshot from the linkedservers on my client, don't know if I had to add all the info for each one, or if they work with the info. they actually have.

ejem, how can I upload an image?

well, here is the description:

SRV_NAME
192.168.254.1
SRV_PROVIDERNAME
SQLOLEDB
SRV_PRODUCT
SQL Server
SRV_DATASOURCE
192.168.254.1
SRV_PROVIDERSTRING
NULL
SRV_LOCATION
NULL
SRV_CAT
NULL

do i need to fill all the info?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources