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.

Viewing 13 posts - 16 through 27 (of 27 total)

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

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

Don't know why it was affect a linked server query, but the only difference I see (other that Standard vs. Enterprise) is that is works on Windows SP1 and not Windows SP2.

Have you tried it on an XP machine or Windows Server 2003?

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 12, 2008 at 8:26 am

#788582

All boxes are Server 2003

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Isn't a DB2 timestamp is listed in seconds since some date? Are we chasing a ghost just because the OP said "it used to work"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

I'm sure you've checked this out, John, but BOL says of connection-level settings...

"SQL Server evaluates datetime constants at run time. A date string that works for the date formats expected by one language may be unrecognizable if the query is executed by a connection using a different language and date format setting. For example, this view works correctly for connections made with the language set to U.S. English, but not for connections made using other languages: "

Have you tried issuing SET DATEFORMAT YMD as the first statement in your batch? I apologise if I'm stating the 'bleedin' obvious' but it hasn't yet been mentioned in this thread.

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 5

All boxes are set the same.

I think I am going to go down the path of getting the boxes in sync and seeing if that gets things in line.

Thanks everyone for their two cents... Once, the box is configured to resemble the other two boxes are will let you know if this resolved the issue

Mike01 SSChampion Points: 11395 More actions · Answer 6

We are still having an issue with this. The interesting thing is when we eliminate the date range in the criteria, the datetime formats are different.

Server that works (Enterprise Edition)

2005-03-11 11:42:42.447

Server that doesn't work (Standard Edtion)

2005-03-11-11.42.42.447431

Note the decimals and dashes on the Standard Edition. The time regional settings on both boxes are the same (h:mm:ss tt). Any ideas?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

You got that from a "SELECT GETDATE()" on both boxes??? If not, what are you using to get this date and what happens if you try "SELECT GETDATE()" on both boxes?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 8

Mike Martin (6/5/2008)
We are still having an issue with this. The interesting thing is when we eliminate the date range in the criteria, the datetime formats are different.
Server that works (Enterprise Edition)
2005-03-11 11:42:42.447
Server that doesn't work (Standard Edtion)
2005-03-11-11.42.42.447431
Note the decimals and dashes on the Standard Edition. The time regional settings on both boxes are the same (h:mm:ss tt). Any ideas?

IMO you aren't using datetime datatyped columns to store this data on your standard edition.

SQLdatetime only goes 0,997 seconds (not 0,997999).

Store the query results in a temp table and you'll find out which datatype is in use ! (implicit conversion changed ?? :crazy: -> that's why you should avoid them !)

So I guess it all comes back to using the correct datatype.

Maybe post the full query (not just 'select * ' )

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 9

I created the temp tables and one created as char(26) and the other as datetime. So, how do I go about fixing this issue?

Mike01 SSChampion Points: 11395 More actions · Answer 10

We resolved this issue. Everything was the same except the ODBC Driver's ini file. The ini file on the one that didn't work was setup to treat timestamp columns as char(26) instead of datetime. Thank you for everyone's responses

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

Glad you resolved the issue.

Can you post the tricky parameter(s) or odbc set ?

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

Mike01 SSChampion Points: 11395 More actions · Answer 12

in the DB2CLI.INI file, there was a setting that looked like this:

Patch1=131072

This code translates to "Describe time stamps as a char(26)". This setting was only on the box that had the Standard Edition, When we removed this setting from the file, the behavior reverted back to treating timestamps as datetime. There are a number of different values for this parameter which can be found in the Advanced settings of the ODBC Driver. Once in the Advanced Settings, Click on the Service tab to see/modify the options. You will need write access to this file to make the changes

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 13

thank you for the feedback.

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