I have a datetime field with a value of '-28049-03-16 10:01:16.267' in SQl Server 2000. I haven't a clue how or why this value was entered as I did not design or write the database. However, I do have the task of writing a DTS package to export the data to Access. The problem is that the DTS fails when it tries to export the record with the above value into an Access table with a Date/Time field. I thought I could get round this by using a UDF to say if the date equals this value then make it null:
DECLARE @CheckDate varChar(300)
SET @CheckDate = CONVERT(varChar(300),@Date)
IF (@CheckDate = '-28049-03-16 10:01:16.267')
SET @Date = Null
However when I use the UDF it gives me the following error:
Server: Msg 542, Level 16, State 1, Procedure UDFn_CheckDate, Line 23
An invalid datetime value was encountered. Value exceeds the year 9999.
I have tried many variations of Convert, Cast etc. but still can't get it to work.
it's possible brett....the one you posted doesn't work (i think) because it sees the first '-' and automatically assumes it's not a value date. and 28048 also seems too large. This however seems to work
CREATE TABLE #tmp (A DATETIME);
INSERT INTO #tmp VALUES (CAST(-28049 AS DATETIME));
SELECT A, CAST(A AS FLOAT) FROM #tmp;
Thanks for your input guys. The database with the funny date values is in fact a read-only version of a live database that is used for reporting only. The data is copied over from the live database to the reporting database at regular intervals. These values only appear in the reporting database and NOT in the original live database - so the data must be corrupting somehow when it is copied across. Anyway, i've passsed the fault onto the software vendors and will have to see what they come up with.
In the meantime, I've managed to get round the problem byt filtering out the suspect dates using something like:
When date < Convert(datetime, '01/01/1809',103)
When date > Convert(datetime, '01/01/2100',103)