The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

Question

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

john_gleason

Mr or Mrs. 500

Points: 553
More actions
March 10, 2008 at 10:02 am

#183885

I have a query the connects to a linked server (DB2) and compares smalldatetime to a db2 timestamp. I receive the error message: The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
I use this same queriy on a different SQL Server box (both SQL 2000) and it works fine. The definition of the linked servers are the same. I did notice the box it does work on has an older SQLSRV32 driver. I am not sure I should load the older driver on the box that doesn't work.
New to SQL Server and looking for help

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

That error means that the DB2 timestamp column date is either before 1900/01/01 or after 2076/06/06. Are you running the linked server query against the same DB2 data? You may want to try converting the smalldatetime to datetime an see if that works.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

john_gleason Mr or Mrs. 500 Points: 553 More actions · Answer 2

john_gleason

Mr or Mrs. 500

Points: 553

March 11, 2008 at 7:52 am

#787981

I am running the query against the same database

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

Could you post the the query, with any proprietary stuff removed of course? It might help me, and others, understand what is going on. I have not worked with DB2 in a while, but wouldn't DateTime be a better match for the Timestamp data type?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

john_gleason Mr or Mrs. 500 Points: 553 More actions · Answer 4

This works on a different SQL Server 2000 box with different version of SQLSRV32.DLL. I believe it is a configuration issue and nothing with the statement. It is the statement

update_timestamp >= @extract_start_date that causes the failure. If I comment out that line the query works.

@extract_start_dateSMALLDATETIME

SELECT *

FROM DB2server..DB2schema.tablename ods

WHERE sta_code = 'V'

AND update_timestamp >= @extract_start_date

AND po_num_pre > @var_zero

AND po_num_mstr >= @var_zero

Jack Corbett SSC Guru Points: 184393 More actions · Answer 5

Where are you setting the value of @extract_start_date and how is it being set? Could be that the error is happening at this point.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

john_gleason Mr or Mrs. 500 Points: 553 More actions · Answer 6

john_gleason

Mr or Mrs. 500

Points: 553

March 11, 2008 at 11:04 am

#788164

The are both the same

2008-03-06 00:00:00.000

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

John

The implicit conversion of update_timestamp to small datetime is causing the problem. Use explicit conversion...

WHERE sta_code = 'V'

AND CONVERT(smalldatetime, update_timestamp, style) >= @extract_start_date

...replace style with the correct code, see BOL.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

john_gleason Mr or Mrs. 500 Points: 553 More actions · Answer 8

Chris,

I understand how to get it to work. The question I have is the other two SQL 2000 boxes the query runs on works fine without any changes.

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 9

- I would advise to use a datetime variable in stead of a smalldatetime.

- probably the other sql2000 run on another servicepack as the one where it fails.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

john_gleason Mr or Mrs. 500 Points: 553 More actions · Answer 10

Would one being Enterprise Edition and the other being Standard Edition cause the issue?

8.00.2039SP4Enterprise Edition

8.00.2039SP4Standard Edition

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 11

I wouldn't think so. (I'd rather suspect it working on a sp3 and failing on a sp4)

Do they realy toutch the same set of data ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

john_gleason Mr or Mrs. 500 Points: 553 More actions · Answer 12

john_gleason

Mr or Mrs. 500

Points: 553

March 12, 2008 at 7:37 am

#788540

Yes the are both linked to the same server

GSquared SSC Guru Points: 260824 More actions · Answer 13

Run "select @@version" on both servers. See if they are running different versions. That's where I'd start my check on this.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

john_gleason Mr or Mrs. 500 Points: 553 More actions · Answer 14

This is the one that doesn't work

This is the one that does work