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?
Thanksif 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?|||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.|||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...|||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?|||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|||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|||cool suggestion!

Would your isdate()=0 default the offending date field to a value other than '01/01/0001'?|||Let me try that Brett, I'll let you know the outcome|||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.|||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)|||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...|||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|||But I was suggesting you create the view on DB2 and reference it from sql server...