Tsql VarChar implicitly to SmallDateTime

Question

When executing the following using a smalldatetime constraint on the returned results, I get zero results:

Execute sp_proc @DateOfBirth =   '01/01/1900'

or

Execute sp_proc @DateOfBirth =   '1900-01-01'

But when using the following varchar argument all the sudden i get results which correspond to 1 Jan 2000. Does smalldatetime implicit conversion not work for mm/dd/YYYY dates and only mm/dd/YY dates?

Execute sp_proc @DateOfBirth =   '01/01/00'

This returns all results for 01/01/2000 birthdays!

CREATE PROCEDURE [dbo].[sp_proc] 
    -- Add the parameters for the stored procedure here

    @DateOfBirth SmallDateTime = null 

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT *
    FROM
     view_People
    WHERE
     FirstName LIKE '%' + IsNull(@FName ,FirstName) + '%'
    AND
     LastName LIKE '%' + IsNull(@LName,LastName) + '%'
    AND
     DOB = IsNull(@DateOfBirth,DOB) 
    AND
     SSN = IsNull(@SSN,SSN)

Answer 1

use a safe format like YYYYMMDD (no dashes)

run these also to see what happens

select convert(smalldatetime,'19000101')
select convert(smalldatetime,'01/01/00')
select convert(smalldatetime,0)

Answer 2

"01/01/00" is converted based on the "two digit year cutoff" option. So it becomes 01 Jan 2000.

If you send '1900-01-01', then your code shows you'll only get results if you have DOB = 01 Jan 1900.

Other than that, it's working as advertised. Unless you want to run Execute sp_proc @DateOfBirth = DEFAULT to force DOB to be ignored

Answer 3

Technically, you should be comparing the output of:

Execute sp_proc @DateOfBirth =   '01/01/00'

to:

Execute sp_proc @DateOfBirth =   '01/01/2000'

or

Execute sp_proc @DateOfBirth =   '2000-01-01'