SQL Server Inserting Date as 1/1/1900

SQL Server inserting Date as 1/1/1900

You have not given it as null, you're trying to insert an empty string (''). You need:

INSERT INTO [ABC] ([code],[updatedate],[flag],[Mfdate]) 
VALUES ('203', '6/12/2013','N/A', NULL)

Although really, if you're going to be inserting dates, best to insert them in YYYYMMDD format, as:

INSERT INTO [ABC] ([code],[updatedate],[flag],[Mfdate]) 
VALUES ('203', '20130612','N/A', NULL)

Blank values in Date column returning as 1900/01/01 on running SELECT statement

You dont need to do the string manipulation as you have shown in your question. If you have dates stored in mm/dd/yyyy format just cast it as DATE.

SELECT cast(a.[PAYOFF DATE] AS DATE) 
FROM MTG a

For 1900-01-01 values, since you are converting from a string data type to Date, String datatype can have Empty strings but Date datatype cannot have empty date values, It can have either a date value or NULL value.

Therefore you need to convert the empty string to nulls before you convert it to date. 1900-01-01 is just a default value sql server puts in for you because Date datatype cannot have an empty value.

You can avoid having this sql server default value by doing something like this.

SELECT cast(NULLIF(a.[PAYOFF DATE],'') AS DATE) 
FROM MTG a

Why SQL query returns '01/01/1900' instead of an empty string when Date field is NULL?

This expression:

select CONVERT(varchar, ISNULL(m.CloseDate, ''), 101) as CloseDate

is more clearly written as:

select CONVERT(varchar(255), COALESCE(m.CloseDate, 0), 101) as CloseDate

That is, the empty string is equivalent to a date value of "0". SQL Server starts counting dates from 1900-01-01, which is why you see that in your results.

I'm not sure what you are expecting. If you want an empty string, assign that after converting the value to a string:

select COALESCE(CONVERT(varchar(255), m.CloseDate, 101), '') as CloseDate

Show empty string when date field is 1/1/1900

When you use a CASE expression (not statement) you have to be aware of data type precedence. In this case you can't just set a DATETIME to an empty string. Try it:

SELECT CONVERT(DATETIME, '');

One workaround is to present your date as a string:

CASE WHEN CONVERT(DATE, CreatedDate) = '1900-01-01' -- to account for accidental time
THEN ''
ELSE CONVERT(CHAR(10), CreatedDate, 120)
+ ' ' + CONVERT(CHAR(8), CreatedDate, 108)
END

Or you could fiddle with the presentation stuff where it belongs, at the presentation tier.

Here is an example that works exactly as you seem to want:

DECLARE @d TABLE(CreatedDate DATETIME);

INSERT @d SELECT '19000101' UNION ALL SELECT '20130321';

SELECT d = CASE WHEN CreatedDate = '19000101'
THEN ''
ELSE CONVERT(CHAR(10), CreatedDate, 120)
+ ' ' + CONVERT(CHAR(8), CreatedDate, 108)
END FROM @d;

Results:

d
-------------------
<-- empty string
2013-03-21 00:00:00

Why does inserting empty string into date column produce 1900-01-01

A NULL date is NULL (unknown value). An empty string, on the other hand, evaluates to 0, which in SQL Server is implicitly an integer representing the number of days since 1900-01-01. You can reference this post.

So when we try to insert '' to a date type column, it is equals to insert 1900-01-01.

SQL table taking 1900-01-01 as a date

Try this one NULLIF SQL2008+

UPDATE animals_table
SET
animalname = 'Mary'
,animaltype = 'Husky'
WHERE
animalid = '123456'
and ISNULL(NULLIF(dategiven,'1900-01-01'),'') = ''

or

 UPDATE animals_table
SET
animalname = 'Mary'
,animaltype = 'Husky'
WHERE
animalid = '123456'
and NULLIF(dategiven,'1900-01-01') IS NULL

Insert date from cell into sql table - Defaults to 1900-01-01

Looks like you are missing single quotes on either side of the date.

INSERT INTO tbl_ExcelUpdate (CellText,CellDate) VALUES ('Some Text ', 24/03/2015)

Should be

INSERT INTO tbl_ExcelUpdate (CellText,CellDate) VALUES ('Some Text ', '24/03/2015')

datetime set 1\1\1900 instead of Null or Blank

instead of '' use NULL

[MOUNT DATE] = 
CASE WHEN T1.[ROUTING]='L&P' then NULL
END
ELSE T1.[A-MOUNT BY]
END

FROM WO T1
INNER JOIN inserted i ON T1.[WO#] = i.[WO#]
END

test through this sample procedure

create table #temp1
(
mydate date null

)

insert #temp1 values (getdate())
select * from #temp1
update #temp1 set mydate = NULL
select * from #temp1

drop table #temp1


Related Topics



Leave a reply



Submit