1900-01-01 problem

I’m pulling a date field into my query. But the null or blank dates show up as ‘1900-01-01’. I can’t make it go away. If I say @field = ‘1900-01-01’ then ‘’ and it still displays that date.

What is this date displayed in as 1900? A report ssrs a form or. ....? Query builder, SSMS?

I’m using Sql Server Management Studio

If I do the following I can get it to display the word 'Null':

cast(NULLIF([Patient Authorization Status Date],'') AS DATE)

and then if I wrap that select with another select and do an isnull([Patient Authorization Status Date],'') I get the following '1900-01-01'

I just want the field to show as blank if there's no true date

Maybe a case isdate(thecolumm) =0 then null else thecolumm end as thedate?

Or case thecolumm is null or thecolumm ='' then null else thecolumm end as thedate

declare @table table ([date] datetime) 


insert into @table

select getdate()
union all 
select '' 

select  
[date]
,case when [date] = '1900-01-01 00:00:00.000' then '' else convert(varchar(50),[date],120) end as [New Date]
from @table

To the OP, remember this about IsNull, the end result will have the same type as the type of the first param, this is why you still get a default date as date types can't be an empty string.

You can't display an empty string, '', in a date column because that's not a valid date. SQL does "domain" checking for every column, that is, the data to be placed in the column must match the data type of the column. If it doesn't, SQL will either reject it or replace it with the default value, as it did in your situation.

Similarly, you can't do this in SQL:
DECLARE @var1 int
SET @var1 = 'ABC'
because 'ABC' is not a valid int.

You can do this:
DECLARE @var1 int
SET @var1 = ''
because SQL treats the blank as zero, not because a blank is allowed.

1 Like

In SSMS - when a column is null (no value = blank) it displays the word NULL to show that the value for column is in fact null and not some other value (empty string).

Why is it an issue to have SSMS show you NULL when the column value is NULL?

Noted with thanks