Function to return valid date from varchar field

Question

We have a function that pulls the date from the first 8 characters of a string. Because of the way this system was written, the users are told they have to enter the date in an 8 character format in the field. For example, 12/06/10 must be entered. The way the original function was written, 12/6/10 or 12/06/2010 would generate a wrong date.

I modified the function to the code below to correct this issue and wonder if anyone has any other suggestions to make this more bullet proof. I'm assuming that returning the datetime will always return a valid date for both 19xx or 20xx. I'm not sure what SQL uses to determine when to use 19xx vs. 20xx and I'm not worried about dates less that 1980 and greater than 2100.

We are still on SQL Server 2000 for this app.

Alter FUNCTION [dbo].[GetDateFromField] ( @FieldString varchar(256) )   
RETURNS datetime AS

BEGIN

    DECLARE @tempResult varchar(8)

    SET @tempResult =  left(@FieldString,8)

    IF isdate(@tempResult) = 0
    BEGIN
        SET @tempResult = NULL
    END

    RETURN @tempResult

END

Answer 1

You could use TRY and CATCH statements to increase robustness.

Answer 2

The "century cut-off" date is controlled by a SQL Server setting - check out this blog post for a great explanation.

Execute this to see your current setting:

sp_configure 'show advanced options', 1
RECONFIGURE

sp_configure 'two digit year cutoff'

You can also change this option as needed.

Answer 3

Only semi-tested on SQL2000 but I think this handles most date-like input.

Alter Function GetDateFromField (
    @FieldString Varchar(256) 
) Returns Datetime 
As 
 Begin 
   Declare @tempResult Char(8), @tempDate as datetime
   If Isdate(@FieldString) = 0
      Set @tempResult = NULL
   Else
    Begin
      Select @tempDate = @FieldString
      Select @tempResult = Convert(Char(8), @tempDate, 1)
   End

   Return @tempResult
End