title: best way to convert and validate a date string
body: <p>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.</p>
  
  <pre><code>DECLARE @Source       char(8)
  DECLARE @Destination  datetime
  
  SET @Source='07152009'
  
  --your solution here
  
  
  SELECT @Destination
  </code></pre>
  
  <p>here is the best way I could think of:</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>EDIT</strong> here's what I'm going to go with...</p>
  
  <pre><code>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
  </code></pre>