CODEWITHSUNDEEP
sqlstringdatetype-conversion
Sam
Sam

Reputation: 766

Convert 'NULL' to Date in SQL

I have a column in my table called startdate. It is in string format. Most of the fields are 'NULL'. I am copying this column to another table which data type is 'Date'. How can I convert all the values from string to Date in SQL. I have tried this code:

INSERT INTO Destination_Table [new_date] 
SELECT CONVERT(DATE,[startdate],103) 
FROM Source_Table

Upvotes: 1

Views: 17618

Answers (2)

Kraang Prime
Kraang Prime

Reputation: 10479

It looks like you are using MSSQL. If you are using MSSQL 2012, the following code should work :

INSERT INTO Destination_Table [new_date] 
SELECT IIF([startdate] = "NULL", null, CONVERT(DATE,[startdate],103)) 
FROM Source_Table

What this does, is use the IIF() method to check the value of [startdate] and if the value is the text "NULL", then return the actual null value which can be allowed in most fields unless you have null disabled on the Destination_Table.[new_date] field.

Since the Date field can only accept and store Date/Time/Date&Time/(actual null) information, the text "NULL" is not valid.

Following is the equivalent for MySQL

INSERT INTO Destination_Table [new_date] 
SELECT IF([startdate] == 'NULL', null, CONVERT(DATE,[startdate],103)) 
FROM Source_Table

(although I am unsure MySQL allows a conversion code as a param to CONVERT() )

Upvotes: 1

David דודו Markovitz
David דודו Markovitz

Reputation: 44931

nullif([startdate],'NULL') returns [startdate] unless it equals to 'NULL' and then it returns NULL (a real NULL, not the string 'NULL')

INSERT INTO Destination_Table [new_date] 
SELECT CONVERT(DATE,nullif([startdate],'NULL'),103) 
from   Source_Table

For learning purposes, here are some expressions with the same results:

nullif(x,y)

case when x=y then null else x end

case x when y then null else x end

Upvotes: 1

Related Questions