Checking for invalid date in SQL Server Stored Procedure

Question

I have a stored procedure in SQL Server 2008 that will insert a record into a table.

The sproc looks similar to this:

    -- params
@f1 int, 
@f2 datetime = null, 
@f3 datetime,
@f4 bit = 0 

BEGIN
    INSERT INTO dbo.table
    (fields......)
    VALUES
    (@params.....)

    RETURN @@IDENTITY
END

Prior to running the INSERT statement, I would like to trap the datetime value of the f2 param. If f2 is not a valid date I would like to set it to null.

I'm fuzzy on the syntax on how to do this. I thought the following would suffice, but not so:

--params
@f1..
@f2..
...

IF(ISDATE(@f2) < 1)
SET @f2= NULL

BEGIN
 INSERT...
 ...
END

What is the syntax for trapping and resetting a param value prior to executing an INSERT?

By the way, the reason that led me to finding a solution to this problem is rooted in the fact that I'm passing a C# minimum date value of "1/1/0001" to SQLS which supports minimum date value of "1/1/1753".

Answer 1

Since @f2 is a datetime it will never hold an invalid date

but just for fun, this is how you would check it

IF ISDATE(@f2) =0
SET @f2= NULL

But like I said this can never be true, if you pass Jan 1 1600 to the proc it will blow up and never execute

if you are on SQL Server 2008 you can use datetime2

Here is an example if you can't change the datetime column, you can still use datetime2 for checking

declare @d datetime2
select @d = '16000101'

select @d,
    convert(varchar(8),@d,112),
    isdate(convert(varchar(8),@d,112))  -- not a valid datetime

Answer 2

It already is a valid datetime because it's been passed via the stored proc parameter. It won't a date before 1753

Note: in SQL Server 2008 you have date and datetime2 which go back to year 0 but ISDATE only works for "old" datetime ranges says MSDN