best way to convert and validate a date string

Question

I have a single char(8) variable formatted as ddmmyyyy in a stored procedure (quality and validity of this value is unknown and beyond my control). What is the best most efficient way to move the value into a datetime variable, and throw an error if it is not valid datetime.

DECLARE @Source       char(8)
DECLARE @Destination  datetime

SET @Source='07152009'

--your solution here


SELECT @Destination

here is the best way I could think of:

DECLARE @Source             char(8)
DECLARE @Temp               varchar(10)
DECLARE @Destination        datetime

set @Source='07152009'
SET @Temp=LEFT(@Source,2)+'/'+SUBSTRING(@Source,3,2)+'/'+RIGHT(@Source,4)

IF ISDATE(@Temp)!=1
BEGIN
    RAISERROR('ERROR, invalid date',16,1)
END
SET @Destination=@Temp

SELECT @Source AS Source, @Temp AS  Temp, @Destination AS Destination

EDIT here's what I'm going to go with...

DECLARE @Source             char(8)
DECLARE @Destination        datetime

set @Source='07152009'
BEGIN TRY
    SET @Destination=CONVERT(datetime,RIGHT(@Source,4)        -- YYYY
                                      +LEFT(@Source,2)        -- MM
                                      +SUBSTRING(@Source,3,2) -- DD
                             )
END TRY
BEGIN CATCH
    PRINT 'ERROR!!!' --I'll add a little more logic here and abort processing
END CATCH

SELECT @Source AS Source, @Destination AS Destination

Answer 1

First of all, since you're using SQL Server 2005, you should put your code that might fail into BEGIN TRY.....END TRY BEGIN CATCH....END CATCH blocks - try/catch blocks for T-SQL!

Second, for all date manipulation, I would always use ISO-8601 format which will work regardless of what current date format is set in SQL Server.

ISO-8601 format is YYYYMMDD for just dates, or YYYY-MM-DDTHH:MM:SS for date with time - so I'd write your code as:

BEGIN TRY
  SET @Source='07152009'
  SET @Temp = RIGHT(@Source, 4) +             -- YYYY
              LEFT(@Source, 2) +              -- MM
              SUBSTRING(@Source, 3, 2)        -- DD

  IF ISDATE(@Temp)!=1
  BEGIN
      RAISERROR('ERROR, invalid date',16,1)
  END

  SET @Destination = CAST(@Temp AS DATETIME)
END TRY
BEGIN CATCH
      -- handle error if something bombs out
END CATCH

Do not rely on any particular date format being set!! Send me your code and I'll try it on a Swiss-German system - I almost guarantee it'll break if you blindly assume "en-US" and thus "mm/dd/yyyy" - it's not the same setting everywhere on this planet.

Unfortunately SQL Server is rather weak handling dates - maybe that might be an extension point where using a CLR assembly inside SQL Server would make sense, to tap into the much richer date handling functions in .NET ??

Marc

PS: seems the ISO-8601 format I knew YYYY-MM-DD doesn't always work in SQL Server - contrary to what Books Online seem to preach. Use YYYYMMDD or YYYY-MM-DDTHH:MM:SS instead.
Thanks, gbn!

Answer 2

You can guarantee date-month-year order using SET DATEFORMAT. This means ISDATE will parse '15-07-2009' as 15th July 2009

Otherwise, your approach is good enough given the external limitations... but you could reorder into ANSI/ISO too.

After marc_s' answer: "SET DATEFORMAT dmy" works for most European settings...

OK:

SET LANGUAGE british
SELECT ISDATE('2009-07-15') --this is ansi says marc_s. It gives "zero"
SELECT ISDATE('2009-07-15T11:22:33') --this really is ANSI and gives true


SET LANGUAGE german
SELECT ISDATE('2009-07-15') --false
SELECT ISDATE('2009-07-15T11:22:33') --true