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