Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Unanswered: Weird DateTime Value

    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:

    RETURNS DateTime

    As
    BEGIN
    DECLARE @CheckDate varChar(300)
    SET @CheckDate = CONVERT(varChar(300),@Date)

    IF (@CheckDate = '-28049-03-16 10:01:16.267')
    BEGIN
    SET @Date = Null
    END
    RETURN (@Date)
    END

    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.

    Anybody got any ideas???

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What kind of datatype is the @Date parameter, and what is the underlying datatype of the column that holds this odd date value?

    Depending upon your answer, two possible solutions are the ISDATE() function, or checking to see if the supplied date value is earlier than 1/1/1900.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    update yourtable set datetimefield = null where pkey = value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    @Date is a DateTime parameter and the underlying table field is also a DateTime value. Have tried the ISDATE() function but I still get the same error.

    Will try loading the data into a Table Variable and running an update query on it. God knows how such a weird value got into this field in the first place.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    datetime?

    I doubt it

    DECLARE @x datetime
    SELECT @x = '-28049-03-16 10:01:16.267'
    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.

  6. #6
    Join Date
    Oct 2003
    Posts
    84
    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;

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well....datetime is stored internally as two integers

    So what you originally posted is not the same as your example...

    SELECT CONVERT(datetime,0)

    Works as well...just no time component
    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
    Oct 2003
    Posts
    84
    my bad.
    I think what I entered is in reality 28xxxx DAYS before jan 1 1900, didn't think it out properly

  9. #9
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    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)
    Then ''
    When date > Convert(datetime, '01/01/2100',103)

    Etc, etc,

    Thanks

Posting Permissions

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