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
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.
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.