Converting null Value with Space

Posted by Shailendra194878 under Sql Server on 6/3/2013 | Points: 10 | Views : 1591 | Status : [Member] | Replies : 3
Hi,
I have one @table in which over 1 thousand records are there i want to convert varchar column to date but problem is that, in those columns couple of columns are empty (null) when i converting varchar to date format it will replace null value with sql default value. what i want is that null value remain null.

Note:- Date column contain either null value of dates columns from selected table.




Responses

Posted by: Pandians on: 6/3/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
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

Posted by: Shailendra194878 on: 6/4/2013 [Member] Starter | Points: 25

Up
0
Down
thanks sir

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

Posted by: Neeraaj.Sharma on: 6/24/2013 [Member] Starter | Points: 25

Up
0
Down


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

Login to post response