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…

Accepted Answer

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!

best way to convert and validate a date string

Solution

Problem

Related problems