Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dr223
Constraint Violating Yak Guru
444 Posts |
 Posted - 2011-04-15 : 10:30:28
|
| Hi, I am working with SQL server 2005, and the table I have has other records having an eventdate field as 01/01/00 00:00:00.000 in reality these fields were empty strings but since datatype was changed from nvarchar to datetime all the empty strings were field with the default date.. which I reckon is 01/01/1900. I want to revert these to NULL values. The field eventdate allows NULL (checkbox allow NULLS ticked). So instead of 01/01/00 00:00:00.000 I want to display NULL. Any help please..Many thanks |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-15 : 10:57:37
|
| select case when eventdate = '19000101' then null else eventdate endupdate tblset eventdate = nullwhere eventdate = '19000101'19000101 is held as 0 in a datetime folumn which is why it is mapped from your empty string.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-04-17 : 01:56:15
|
| if you're doing this via insert proc use NULLIF to convert 0 to NULL------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Change Datetime to NULL value