Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2010
    Posts
    10

    Question Unanswered: Case When Null Date ISSUE (Challenge)

    I have a table where the date has a null value it displays null. I am updating the field to '' where it's null. However it defaults to a
    '1900-01-01 00:00:00.000' value. To correct this I used.

    Select CUSTOMERNUMBER,
    CASE WHEN Medical_Record='1900-01-01 00:00:00:000' THEN '' ELSE CAST(Medical_Record AS varchar(32)) END
    , Logbook
    From Finance.dbo.temp_EDPScanned
    Where Medical_Record >= CreateDate or Logbook >=CreateDate

    GROUP BY CUSTOMERNUMBER,Medical_Record,Logbook

    The Result is Aug 29 2012 12:00AM

    When creating a report in SSRS or excel it will not format to a date. Is there any way to get around this? I have looked evrywhere.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dates are stored as numbers. You're trying to update them to a zero-length string.
    SQL Server really should throw an error on this, but instead it updates them to zero, which is the date that you are seeing as a result.
    A flaw in SQL Server, in my opinion.
    Why can't you leave the NULLs in the date field? Converting them to strings in the database is not a good idea.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Dec 2010
    Posts
    10
    Darn, I was hoping there was an answer!!!! Thanks for the info, I will keep the NULL value

Posting Permissions

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