Do not display "1900-01-01 00:00:00.000"

Question

Do not display "1900-01-01 00:00:00.000"

adonetok

SSCrazy Eights

Points: 8395
More actions
August 1, 2011 at 12:35 pm

#97912

How to void displaying ''1900-01-01 00:00:00.000" if datetime data is null?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

where is that displaying?

zero (0) is SQL's starting date of 1900-01-01, so i suspect that either your query or your application is converting null to zero,a dn displaying that converted datetime value.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If the datetime is null, SQL will display it as null. It doesn't automatically convert any null value to a non-null value. Something in the query or the display is, or the column is a varchar and = '' (rather than null), which when converted to datetime goes to the 0 date (iirc).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

adonetok SSCrazy Eights Points: 8395 More actions · Answer 3

Someone creaete a table in which "1900-01-01 00:00:00.000" are in one column (only 30% have real date).

How to code to convert them into null in my new table?

Lowell SSC Guru Points: 323518 More actions · Answer 4

will the column support NULLS? if it can, you could update the data to be null where the column is that value.

it really depends on the table and the columns' datatype .

also, will your application crash if it gets a null value for that field, assuming it's trying to populate a datepicker control or something?

UPDATE YOURTABLE

SET ThatColumn = NULL

WHERE ThatColumn = '1900-01-01 00:00:00.000'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

UPDATE <table>

SET <column name> = NULL

WHERE <column name> = '1900-01-01'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736544 More actions · Answer 6

update table

set datecolfield = null

where datecolfield = '1900-01-01 00:00:00.000'

adonetok SSCrazy Eights Points: 8395 More actions · Answer 7

adonetok

SSCrazy Eights

Points: 8395

August 1, 2011 at 12:51 pm

#1361786

Yes, it works great.

Thank all of you.