Thursday, March 29, 2012

Converting empty string to Null when inserting/updating

I am using the following query to calculate date differences:
select ........DATEDIFF(d, recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM ....

I have stored all my dates as NVARCHAR because of the issues with localization.
If the value is an empty String my output is eg: -38700. which is way off and incorrect. Some of the values in my table areNULL and they produce the correct result.

Is there a T-SQL statement to replace empy Strings with the NULL value in my tables.
I'd like to use it as a trigger when inserting or updating to convert empty strings to NULL
before the values are inserted.

Thanks guys.

You REALLY should store your dates as a datetime. There is no localization "Problem" with datetimes if you use them correctly, and you can't sort and/or generate good indexes if they are stored in a nvarchar field (Unless you specifically use the YYYYMMDD or YYYY-MM-DD format).

That aside, yes, try NULLIF() like:

INSERT INTO MyTable(col1) VALUES (NULLIF(@.val1,''))

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_nos-nz_3uhy.asp

No comments:

Post a Comment