Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2004
    Posts
    22

    Unanswered: Dateime valid ranges

    I've got a linked server setup to DB2, and some of the
    date fields in the DB contain 1/1/0001 values.

    I've got views created in SQL2000 against the DB2 linked server.

    When I run a query against a particular table that contains multiple field of datetime type.

    I get the below error
    Server: Msg 8114, Level 16, State 8, Line 1
    Error converting data type DBTYPE_DBTIMESTAMP to datetime.

    This only happens when I include in the select the field that contains 1/1/0001 values.

    I assume since valid dates ranges in SQL are from
    January 1, 1753 through December 31, 9999, this would be
    what's causing this.

    I tried to covert in the select but that failed as well. The only thing that I've been able to do, is to use a DTS to pull the data from the DB2 to a local SQL2000 table, with that fields type set as varchar. This works.

    Using a DTS to pull the data to a local table in production isn't a viable workaround, since this table contains 1.8 million rows.

    How is DTS converting this field, when convert fails in the select?

    How do I get around this?
    Thanks

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    if you convert the DB2 attribute to (v)char rather than a date does your query work?

    If that works could you convert to string then use a case statment to move the date to the epoc for SQL server?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2004
    Posts
    22
    Convert the field to vchar on the DB2 side?

    This is the only field from the DB2 that I'm having issue wth, I have other fields that are also datetime and all other fields can be retrieved.

    I just can't retrieve this one in particular, because it contains 1/1/0001 values, outside the valid SQL2000 date range.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about...Add a predicate so the result set only returns valid data, and UNION it to a select where the date column is a literal...since it's unusable anyway...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    convert the attribute in your select. I am wondering if the above mentioned error occures when converting the db2 date to a sql date. If that is the case why not convert it to a (v)char? Or more precisly, use a case statment to move the 01/01/0001 date to (v)char that you cna test and if needed replace the date with an 01/01/1753 date?
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Jan 2004
    Posts
    22
    I tried to convert it to vchar, it failed as well.

    I don't think your case idea will work either, since I won't be able to get teh value to test against.

    I conditionally need this fields values, so I can include that condition, Union as was mentioned in a prior post and just have a place holder in my first slect.

    This select is pretty nasty, it already contains one union, so now it looks like it's going to have three. Another for each of the existing selects, ouch.

    With the size of this select, I may split it apart, that way I only have to do the union on the one specific field, and the db again as I go thru the first result set. Not prime I know, but sometimes you gotta work around issues.

    Thanks for the ideas

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No Create a view on that table

    CREATE VIEW myView99
    AS
    SELECT Col list
    FROM myTable99
    WHERE ISDATE(DB2dateCol)=1
    UNION ALL
    SELECT Col list
    FROM myTable99
    WHERE ISDATE(DB2dateCol)=0
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    cool suggestion!

    Would your isdate()=0 default the offending date field to a value other than '01/01/0001'?
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Jan 2004
    Posts
    22
    Let me try that Brett, I'll let you know the outcome

  10. #10
    Join Date
    Jan 2004
    Posts
    22
    CREATE VIEW dbo.blspt_test
    AS
    SELECT ordid, mgfordt
    FROM blspt
    WHERE ISDATE(mgfordt)=1
    and blbatid = 790
    UNION ALL
    SELECT ordid,''
    FROM blspt
    WHERE ISDATE(mgfordt)=0
    and blbatid = 790

    select * from blspt_test

    Same error

    Server: Msg 8114, Level 16, State 8, Line 1
    Error converting data type DBTYPE_DBTIMESTAMP to datetime.


    Great suggestion though.

  11. #11
    Join Date
    Jan 2004
    Posts
    22
    This is the select without the added unions that I'll have to add because of the date issue.

    SELECT dbo.BLSPT.STNID,
    dbo.BLSPT.ORDID,
    dbo.BLSPT.ORDLNNUM,
    dbo.BLSPT.ORDLNTYP,
    dbo.BLSPT.ORDLNSEQ,
    dbo.BLSPT.ALTLOG,
    dbo.BLSPT.SPTNUM,
    dbo.BLSPT.SPTLEN,
    dbo.BLSPT.EXTCPYNUM,
    dbo.BLSPT.PREDSCR,
    dbo.BLSPT.RT,
    dbo.BLSPT.BCSTDT,
    dbo.BLSPT.AIRTIM,
    dbo.BLSPT.SPTCHR,
    dbo.BLSPT.BCSTTIM,
    dbo.BUYUNTHDR.BUYSNAM,
    dbo.BUYUNTHDR.BUYLNAM,
    dbo.BUYUNTHDR.STRTIM,
    dbo.BUYUNTHDR.ETIM,
    dbo.ORDLN.SPTCHR,
    dbo.ORDLN.SPTPAT,
    dbo.OFC.SOFNAM,
    dbo.ORDHDR.ACCTTYP,
    dbo.STF.FSTNAM,
    dbo.STF.LSTNAM,
    dbo.PROP_HDR.DEAL_ID
    FROM dbo.STF RIGHT OUTER JOIN
    dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX RIGHT OUTER JOIN
    dbo.BLSPT INNER JOIN
    dbo.ORDLN ON dbo.BLSPT.ORDID = dbo.ORDLN.ORDID AND dbo.BLSPT.ORDLNNUM = dbo.ORDLN.ORDLNNUM AND dbo.BLSPT.ORDLNTYP = dbo.ORDLN.ORDLNTYP AND
    dbo.BLSPT.ORDLNSEQ = dbo.ORDLN.ORDLNSEQ INNER JOIN
    dbo.ORDHDR ON dbo.ORDLN.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID AND
    dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID LEFT OUTER JOIN
    dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID LEFT OUTER JOIN
    dbo.BUYUNTHDR ON dbo.ORDLN.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
    WHERE (dbo.BLSPT.BLBATID = @batch)

    UNION

    SELECT dbo.BLSPT.STNID,
    dbo.BLSPT.ORDID,
    dbo.BLSPT.ORDLNNUM,
    dbo.BLSPT.ORDLNTYP,
    dbo.BLSPT.ORDLNSEQ,
    dbo.BLSPT.ALTLOG,
    dbo.BLSPT.SPTNUM,
    dbo.BLSPT.SPTLEN,
    dbo.BLSPT.EXTCPYNUM,
    dbo.BLSPT.PREDSCR,
    dbo.BLSPT.RT,
    dbo.BLSPT.BCSTDT,
    dbo.BLSPT.AIRTIM,
    dbo.BLSPT.SPTCHR,
    dbo.BLSPT.BCSTTIM,
    dbo.BUYUNTHDR.BUYSNAM,
    dbo.BUYUNTHDR.BUYLNAM,
    dbo.BUYUNTHDR.STRTIM,
    dbo.BUYUNTHDR.ETIM,
    8 as sptchr,
    '' as sptpat,
    dbo.OFC.SOFNAM,
    dbo.ORDHDR.ACCTTYP,
    dbo.STF.FSTNAM,
    dbo.STF.LSTNAM,
    dbo.PROP_HDR.DEAL_ID
    FROM dbo.STF
    RIGHT OUTER JOIN dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX
    RIGHT OUTER JOIN dbo.BLSPT
    INNER JOIN dbo.ORDLNNT ON dbo.BLSPT.ORDID = dbo.ORDLNNT.ORDID
    INNER JOIN dbo.ORDHDR ON dbo.ORDLNNT.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID
    AND dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID
    LEFT OUTER JOIN dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID
    LEFT OUTER JOIN dbo.BUYUNTHDR ON dbo.BLSPT.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
    WHERE (dbo.BLSPT.BLBATID = @batch)

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by jtn916
    CREATE VIEW dbo.blspt_test
    AS
    SELECT ordid, mgfordt
    FROM blspt
    WHERE ISDATE(mgfordt)=1
    and blbatid = 790
    UNION ALL
    SELECT ordid,''
    FROM blspt
    WHERE ISDATE(mgfordt)=0
    and blbatid = 790

    select * from blspt_test

    Same error

    Server: Msg 8114, Level 16, State 8, Line 1
    Error converting data type DBTYPE_DBTIMESTAMP to datetime.


    Great suggestion though.
    Damn...

    OK...do you have any say over the DB2 Box?

    Maybe you can put the view there....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jan 2004
    Posts
    22
    I can create tables on the db2 but, when it's updated, which is often, they wipe out all that doesn't belong to the DB, according to "them", thus any tables or views I have would be deleted.

    I could however check for existence of the needed view, if it doesn't exist, create it in my app, and go on.

    I think I'll just go with my SP that I have working.

    if you curious


    Thanks for your time.

    I have another interesting problem in relationship to DB2 linked servers, views and SQL2000 on 2003 server. I posted it here as well the other day, but got no replies.

    In short, a view created against a linked db2 server in sql2000 on 2003 server, will only return 512k of data in a result set when you run a query against it. This is not the case if done on 2000 server. I gave up on that one, and moved all my crap over to a 2000 box.


    Thanks for your time, if you hear of a better solution, I'd be interested in hearing it. In relationship to this date issue.

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    --IF BLSPT.ORDLNTYP = 2 then it's a make good
    --Since paradigm stores default values in the mgfordt field
    -- as 1/1/0001, and sql pukes on that date, because it's outside
    --the valid date range, we'll add unions to handle this.
    --One select with a place holder 01/01/1900 for the mgfordt if it's not a
    --makegood, and get mgfordt if it is


    ALTER procedure dbo.sp_blspt_extract
    @batch as integer
    as

    SELECT dbo.BLSPT.STNID,
    dbo.BLSPT.ORDID,
    dbo.BLSPT.ORDLNNUM,
    dbo.BLSPT.ORDLNTYP,
    dbo.BLSPT.ORDLNSEQ,
    dbo.BLSPT.ALTLOG,
    dbo.BLSPT.SPTNUM,
    dbo.BLSPT.SPTLEN,
    dbo.BLSPT.EXTCPYNUM,
    dbo.BLSPT.PREDSCR,
    cast('01/01/1900' as datetime) as MGFORDT,
    dbo.BLSPT.RT,
    dbo.BLSPT.BCSTDT,
    dbo.BLSPT.AIRTIM,
    dbo.BLSPT.SPTCHR,
    dbo.BLSPT.BCSTTIM,
    dbo.BUYUNTHDR.BUYSNAM,
    dbo.BUYUNTHDR.BUYLNAM,
    dbo.BUYUNTHDR.STRTIM,
    dbo.BUYUNTHDR.ETIM,
    dbo.ORDLN.SPTCHR,
    dbo.ORDLN.SPTPAT,
    dbo.OFC.SOFNAM,
    dbo.ORDHDR.ACCTTYP,
    dbo.STF.FSTNAM,
    dbo.STF.LSTNAM,
    dbo.PROP_HDR.DEAL_ID
    FROM dbo.STF RIGHT OUTER JOIN
    dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX RIGHT OUTER JOIN
    dbo.BLSPT INNER JOIN
    dbo.ORDLN ON dbo.BLSPT.ORDID = dbo.ORDLN.ORDID AND dbo.BLSPT.ORDLNNUM = dbo.ORDLN.ORDLNNUM AND dbo.BLSPT.ORDLNTYP = dbo.ORDLN.ORDLNTYP AND
    dbo.BLSPT.ORDLNSEQ = dbo.ORDLN.ORDLNSEQ INNER JOIN
    dbo.ORDHDR ON dbo.ORDLN.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID AND
    dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID LEFT OUTER JOIN
    dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID LEFT OUTER JOIN
    dbo.BUYUNTHDR ON dbo.ORDLN.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
    WHERE (dbo.BLSPT.BLBATID = 790)
    --AND (NOT (RTRIM(STNID) IN ('INTUSA', 'INTCAN')))
    --CLIEN ACCESS pukes on this one, I'll wead out the few I don't need in code
    AND DBO.BLSPT.ORDLNTYP <> 2 --Not a makegood

    UNION

    SELECT dbo.BLSPT.STNID,
    dbo.BLSPT.ORDID,
    dbo.BLSPT.ORDLNNUM,
    dbo.BLSPT.ORDLNTYP,
    dbo.BLSPT.ORDLNSEQ,
    dbo.BLSPT.ALTLOG,
    dbo.BLSPT.SPTNUM,
    dbo.BLSPT.SPTLEN,
    dbo.BLSPT.EXTCPYNUM,
    dbo.BLSPT.PREDSCR,
    cast('01/01/1900' as datetime) as MGFORDT,
    dbo.BLSPT.RT,
    dbo.BLSPT.BCSTDT,
    dbo.BLSPT.AIRTIM,
    dbo.BLSPT.SPTCHR,
    dbo.BLSPT.BCSTTIM,
    dbo.BUYUNTHDR.BUYSNAM,
    dbo.BUYUNTHDR.BUYLNAM,
    dbo.BUYUNTHDR.STRTIM,
    dbo.BUYUNTHDR.ETIM,
    8 as sptchr,
    '' as sptpat,
    dbo.OFC.SOFNAM,
    dbo.ORDHDR.ACCTTYP,
    dbo.STF.FSTNAM,
    dbo.STF.LSTNAM,
    dbo.PROP_HDR.DEAL_ID
    FROM dbo.STF
    RIGHT OUTER JOIN dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX
    RIGHT OUTER JOIN dbo.BLSPT
    INNER JOIN dbo.ORDLNNT ON dbo.BLSPT.ORDID = dbo.ORDLNNT.ORDID
    INNER JOIN dbo.ORDHDR ON dbo.ORDLNNT.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID
    AND dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID
    LEFT OUTER JOIN dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID
    LEFT OUTER JOIN dbo.BUYUNTHDR ON dbo.BLSPT.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
    WHERE (dbo.BLSPT.BLBATID = @batch)
    AND DBO.BLSPT.ORDLNTYP <> 2 --Not a makegood

    UNION --Now we'll get the makegoods

    SELECT dbo.BLSPT.STNID,
    dbo.BLSPT.ORDID,
    dbo.BLSPT.ORDLNNUM,
    dbo.BLSPT.ORDLNTYP,
    dbo.BLSPT.ORDLNSEQ,
    dbo.BLSPT.ALTLOG,
    dbo.BLSPT.SPTNUM,
    dbo.BLSPT.SPTLEN,
    dbo.BLSPT.EXTCPYNUM,
    dbo.BLSPT.PREDSCR,
    dbo.BLSPT.MGFORDT,
    dbo.BLSPT.RT,
    dbo.BLSPT.BCSTDT,
    dbo.BLSPT.AIRTIM,
    dbo.BLSPT.SPTCHR,
    dbo.BLSPT.BCSTTIM,
    dbo.BUYUNTHDR.BUYSNAM,
    dbo.BUYUNTHDR.BUYLNAM,
    dbo.BUYUNTHDR.STRTIM,
    dbo.BUYUNTHDR.ETIM,
    dbo.ORDLN.SPTCHR,
    dbo.ORDLN.SPTPAT,
    dbo.OFC.SOFNAM,
    dbo.ORDHDR.ACCTTYP,
    dbo.STF.FSTNAM,
    dbo.STF.LSTNAM,
    dbo.PROP_HDR.DEAL_ID
    FROM dbo.STF RIGHT OUTER JOIN
    dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX RIGHT OUTER JOIN
    dbo.BLSPT INNER JOIN
    dbo.ORDLN ON dbo.BLSPT.ORDID = dbo.ORDLN.ORDID AND dbo.BLSPT.ORDLNNUM = dbo.ORDLN.ORDLNNUM AND dbo.BLSPT.ORDLNTYP = dbo.ORDLN.ORDLNTYP AND
    dbo.BLSPT.ORDLNSEQ = dbo.ORDLN.ORDLNSEQ INNER JOIN
    dbo.ORDHDR ON dbo.ORDLN.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID AND
    dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID LEFT OUTER JOIN
    dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID LEFT OUTER JOIN
    dbo.BUYUNTHDR ON dbo.ORDLN.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
    WHERE (dbo.BLSPT.BLBATID = @batch)
    --AND (NOT (RTRIM(STNID) IN ('INTUSA', 'INTCAN')))
    --CLIEN ACCESS pukes on this one, I'll wead out the few I don't need in code
    AND DBO.BLSPT.ORDLNTYP = 2 --makegood

    UNION

    SELECT dbo.BLSPT.STNID,
    dbo.BLSPT.ORDID,
    dbo.BLSPT.ORDLNNUM,
    dbo.BLSPT.ORDLNTYP,
    dbo.BLSPT.ORDLNSEQ,
    dbo.BLSPT.ALTLOG,
    dbo.BLSPT.SPTNUM,
    dbo.BLSPT.SPTLEN,
    dbo.BLSPT.EXTCPYNUM,
    dbo.BLSPT.PREDSCR,
    dbo.BLSPT.MGFORDT,
    dbo.BLSPT.RT,
    dbo.BLSPT.BCSTDT,
    dbo.BLSPT.AIRTIM,
    dbo.BLSPT.SPTCHR,
    dbo.BLSPT.BCSTTIM,
    dbo.BUYUNTHDR.BUYSNAM,
    dbo.BUYUNTHDR.BUYLNAM,
    dbo.BUYUNTHDR.STRTIM,
    dbo.BUYUNTHDR.ETIM,
    8 as sptchr,
    '' as sptpat,
    dbo.OFC.SOFNAM,
    dbo.ORDHDR.ACCTTYP,
    dbo.STF.FSTNAM,
    dbo.STF.LSTNAM,
    dbo.PROP_HDR.DEAL_ID
    FROM dbo.STF
    RIGHT OUTER JOIN dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX
    RIGHT OUTER JOIN dbo.BLSPT
    INNER JOIN dbo.ORDLNNT ON dbo.BLSPT.ORDID = dbo.ORDLNNT.ORDID
    INNER JOIN dbo.ORDHDR ON dbo.ORDLNNT.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID
    AND dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID
    LEFT OUTER JOIN dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID
    LEFT OUTER JOIN dbo.BUYUNTHDR ON dbo.BLSPT.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
    WHERE (dbo.BLSPT.BLBATID = @batch)
    AND DBO.BLSPT.ORDLNTYP = 2 --makegood
    ORDER BY dbo.BLSPT.BCSTDT ASC,
    dbo.BLSPT.AIRTIM ASC



    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    But I was suggesting you create the view on DB2 and reference it from sql server...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •