0

I am running an OpenQuery in SQL Server against a proprietary database which has unfortunately chosen to be overly modern and support 5 digit years and users being users do manage to record them. The code I have written is working, but gets unduly complicated as there are many dates in the vehicle table. My working code has the flavour of:

select 
    vehicle
,   cast (recdate as varchar) as recdate
from OPENQUERY([TEST], 
'SELECT 
    tbl.vehicle,
    tbl.recdate
FROM vehicles tbl
WHERE (tbl.recdate <= {d ''9999-12-31''})
')
union
select 
    vehicle
,   '5 digit date' as recdate
from OPENQUERY([TEST], 
'SELECT 
    tbl.vehicle,
    tbl.recdate
FROM Vehicles tbl
WHERE (tbl.recdate > {d ''9999-12-31''})
')

My attempts to test recdate outside of the openquery produce an "Error converting data type DBTYPE_DBDATE to date". I've tried both is_date() and try_cast(), but neither are designed to deal with a invalid date that is already in a date field.

Within the open query, I have tried using CONVERT, CAST and CASE statements, but these do not appear to be supported by the proprietary database.

Any suggestions?

Regards HamsterJam

HamsterJam
  • 1
  • I suspect the issue is that openquery is attempting to convert the bad dates to date BEFORE it evaluates the select list and ignores that bad date column entirely. I suggest you replace that column with your "error" literal (or perhaps convert it to some string in the query you pass to OPENQUERY so you can "see" the bad values). – SMor Nov 09 '21 at 16:53
  • `year(tbl.recdate) > 9999` perhaps? – AlwaysLearning Nov 09 '21 at 21:35
  • No - I think the problem is that the query you execute at the remote database is returning a resultset containing your 5 digits year and SQL Server sees the datatype of that as date and cannot convert it correctly (because it's nonsense). So that query should select the "nonsense" rows without including that column directly in some fashion. Perhaps replace it with a string of whatever value you want (e.g., "5 digit date"), convert the column to a string so you can see it, or null - but it must be done within the query. – SMor Nov 10 '21 at 04:03
  • And the first thing to do is identify and validate the correct syntax to use for your other database engine. You will likely do this with the development tools used for that platform - not SSMS. And please learn the very significant difference between UNION and UNION ALL. Most likely you should use UNION ALL since it seems unlikely your query can have duplicate rows. – SMor Nov 10 '21 at 04:07
  • Thanks @SMor. Absolutely yes to UNION ALL - well spotted and your understanding of the issue is spot on. The available syntax within the other database is unfortunately difficult to resolve as we are not developers on this platform, but it does seems to be the only sensible solution to progress. – HamsterJam Nov 10 '21 at 10:30

0 Answers0