Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Do not display "1900-01-01 00:00:00.000" Expand / Collapse
Author
Message
Posted Monday, August 1, 2011 12:35 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:06 PM
Points: 308, Visits: 583
How to void displaying ''1900-01-01 00:00:00.000" if datetime data is null?
Post #1152065
Posted Monday, August 1, 2011 12:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 12,906, Visits: 31,985
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1152066
Posted Monday, August 1, 2011 12:39 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 42,774, Visits: 35,871
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 2008, MVP
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

Post #1152068
Posted Monday, August 1, 2011 12:43 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:06 PM
Points: 308, Visits: 583
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?
Post #1152071
Posted Monday, August 1, 2011 12:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 12,906, Visits: 31,985
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1152078
Posted Monday, August 1, 2011 12:47 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 42,774, Visits: 35,871
UPDATE <table>
SET <column name> = NULL
WHERE <column name> = '1900-01-01'



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1152079
Posted Monday, August 1, 2011 12:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:06 PM
Points: 33,169, Visits: 15,304
update table
set datecolfield = null
where datecolfield = '1900-01-01 00:00:00.000'








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1152080
Posted Monday, August 1, 2011 12:51 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:06 PM
Points: 308, Visits: 583
Yes, it works great.
Thank all of you.
Post #1152086
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse