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

  • 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

  • All boxes are Server 2003

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

  • 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

  • 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/

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

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

  • 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/

  • 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

  • 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/

  • 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

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

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