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

  • john_gleason

    SSC-Addicted

    Points: 453

    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

  • Jack Corbett

    SSC Guru

    Points: 184372

    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

    SSC-Addicted

    Points: 453

    I am running the query against the same database

  • Jack Corbett

    SSC Guru

    Points: 184372

    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

    SSC-Addicted

    Points: 453

    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_date SMALLDATETIME

    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: 184372

    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

    SSC-Addicted

    Points: 453

    The are both the same

    2008-03-06 00:00:00.000

  • ChrisM@Work

    SSC Guru

    Points: 186045

    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

    [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

  • john_gleason

    SSC-Addicted

    Points: 453

    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: 134265

    - 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


    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[/url] :alien: but most of the time this is me :hehe:

  • john_gleason

    SSC-Addicted

    Points: 453

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

    8.00.2039 SP4 Enterprise Edition

    8.00.2039 SP4 Standard Edition

  • Johan Bijnens

    SSC Guru

    Points: 134265

    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


    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[/url] :alien: but most of the time this is me :hehe:

  • john_gleason

    SSC-Addicted

    Points: 453

    Yes the are both linked to the same server

  • GSquared

    SSC Guru

    Points: 260824

    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

    SSC-Addicted

    Points: 453

    This is the one that doesn't work

    Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    This is the one that does work

    Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

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

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