Converting null Value with Space

1. EMPTY and NULL are different!

According to your case - you have EMPTY String instead of NULL. Thats why when the data had been converted to Initial DATE VALUE instead of NULL

So, We can use CASE Statement to replace NULL instead of '' (EMPTY String)

Select CAST(CASE Column1 WHEN '' THEN NULL ELSE Column1 END AS DATETIME) from TableName

Go

Please get back, If your problem still persists!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Shailendra194878, if this helps please login to Mark As Answer. | Alert Moderator

thanks sir

Shailendra194878, if this helps please login to Mark As Answer. | Alert Moderator

Datetime starts from 1900-01-01 00:00:00.000
and in charcter Blank '' converts to its starting value,
Null is undifine SO
Null will remain Null in the result abd blank converts in datetime`s default value


i guess you want to alter the datatype of an Existing table so lets create a acenario


Create TAble #dataissue (Currentdate varchar(40))

Insert into #dataissue values (null )
Insert into #dataissue values ('')

Insert into #dataissue values ('1-mar-2013')


Now table has all the values, and you have to convert all the Blank value to
Null values , if you won`t those blank will imlpictly convert into "1900-01-01 00:00:00.000 "

make sure all your existing Store procedures and Querys those are inserting data into table
should not passing the blank values to column.

Step 1 :
Update all blank values to Null

Update #dataissue Set Currentdate = Null where Currentdate = ''
GO

Step 2 :

now change the datatype

ALTER TABLE #dataissue ALTER COLUMN Currentdate datetime
GO
REF : http://www.tutorialsqlserver.com/Create/Create-Table-in-Sql-Server.htm

Now check the result

Select * From #dataissue





Visit my Blog


http://www.tutorialsqlserver.com/

Shailendra194878, if this helps please login to Mark As Answer. | Alert Moderator