Send a empty DateTime value into SQL stored procedure. But not null

Question

I have an SQL stored procedure which accepts a DateTime parameter which has a default value of NULL

@pmNext_Check_Date DATETIME=NULL

I want to use this parameter in 3 scenarios:

1. If it's NULL then don't update any records
2. If it's got a date value then update all my records specified in my WHERE clause
3. The problem one! Set all the date fields in my query to NULL for the records in my WHERE clause.

Here is the block of code within the SP that is causing me issues (the rest of the UPDATE statement is build elsewhere in the SP and works fine):

IF @pmNext_Check_Date IS NOT NULL
    IF @pmNext_Check_Date ='' --This is the bit that is causing me a problem. I just need to check for a empty date
        SET @sql = @sql + ' Next_Check_Date = NULL '
    ELSE
        SET @sql = @sql + ' Next_Check_Date = @pmNext_Check_Date '                    

SET @sql = @sql + ' WHERE ID IN (1, 2)'

So for example if I have the following 2 rows:

ID NextCheckDate

1 12/12/12

2 NULL

In scenario 1 I wouldn't pass the parameter in as the procedure will use the default value and no dates will be updated.

In scenario 2 I pass in a date value and update both rows with the date value

In scenario 3 I want to update the date value on my rows to be null. The difference between scenario 1 & 3 is in scenario 3 the user will be choosing to set the date values to null.

So, I wanted to pass a blank date into the stored procedure. I'm doing this from C# and would like to do something like the following:

SqlParameter param = new SqlParameter("@pmNext_Check_Date", "");                            

This fails as the SP is expecting a DateTime.

So I want to be able to pass in a blank date and also how do I check this within the SP. The current check which is below doesn't work:

IF @pmNext_Check_Date =''

Thanks in advance. Hope this all makes sense. I'm using C#4.0 and SQL 2008

Answer 1

There is no such thing as a "blank date". You could use a well-known sentinel value (01 Jan for some arbitrary ancient year, for example), but null would be preferable. Note that to pass an explicit null via a parameter, you need:

SqlParameter param = new SqlParameter("@pmNext_Check_Date", DBNull.Value);

If that doesn't have enough granularity, consider adding a separate boolean (bit) parameter (or similar) that clarifies what you want the sproc to do. Or: have multiple procs to do these different things.

One useful sentinel value for SQL Server is January 1, 1753 (SQL Server's minimum datetime value) - this can be produced in TSQL without string parsing as cast(-53690 as datetime).

Answer 2

You can't pass an empty string as a datetime. So, you have a couple options. You could add an additional parameter to indicate whether or not an update should occur. I suggest that as the best options for code readability and maintainability. The other option would be to pass in the parameter as a string and parse it. That way you could use your empty string concept.

Sql Server string to date conversion

Answer 3

Sorry, there is no way to do precisely what you're asking. A DATETIME value is either NULL or a valid date, there is no "empty" like there is with strings.

Another workaround is to pass a token value (that wouldn't be a valid date otherwise) to represent your so-called empty string, e.g. a common one I've seen used is 1900-01-01. Then you can differentiate in your stored procedure between NULL and "empty."

But I don't recommend doing this at all. I agree with the other suggestions: add another parameter and do the logic in a more meaningful way in the stored procedure.

Answer 4

If I can recall correctly, columns with a DATETIME datatype which allow NULLS will default to the value 1900-01-01 instead of a BLANK value.

For example: NULLS may be allowed for certain columns which might receive a value later down the line based on some kind of business logic. I have seen folks keep these open ended columns as varchar to enable a custom entry of some type or an empty string, something which datetime will not allow.

If you ask me I'd try not to mess around with the base column's data type and let it remain as DATETIME. For data retrieval and reporting purposes we might try the following approach, which may not be the best way to do it. But it works.

BEGIN
            DECLARE @VarcharDateTable TABLE
            ([EndDate_Varchar] varchar(27))

        BEGIN
                INSERT INTO @VarcharDateTable([EndDate_Varchar])
            SELECT 
              CONVERT(varchar(27), [EndDate_Datetime], 121)        
                FROM [dbo].[MainTable]
        END
        BEGIN
        SELECT CASE WHEN [EndDate_Varchar] LIKE '1900-01-01%'
                        THEN 'Data unavailable'--or whatever you want
                        ELSE [EndDate_Varchar]
                   END AS [EndDate_Varchar] FROM @VarcharDateTable
        END 
END