Convert European dd/mm/yyyy values to US mm/dd/yyyy

Hi all

My current client is a travel agency that is running 2008R2, which means TRY_CAST and TRY_CONVERT are not available.

I have an SSIS process where I receive .csv's from multiple countries that have a matching file spec, a date column stored as a varchar(10), but the value can be either dd/mm/yyyy or mm/dd/yyyy depending on the country.

Question:  What's the most easy/graceful way to convert from dd/mm/yyyy or mm/dd/yyyy ?

Right now I'm using the below function which conditionally parses the string to YYYYMMDD, but I'm guessing this can be improved.

CREATE FUNCTION dbo.fnConvertEuropeanDateToUS (@dt varchar(10), @CountryCode char(2)) 
RETURNS date 
AS 

begin

-- Convert European dates dd/mm/yyyy to YYYYMMDD

-- TESTING ONLY
-- Declare @dt varchar(10) = '25/05/2016'

Declare @dt_new varchar(12), @ret date

-- Strip out the /'s and -'s
SET @dt = REPLACE(REPLACE(@dt, '-', ''), '/', '') 

IF @CountryCode = 'AU' OR @CountryCode = 'DK' OR @CountryCode = 'EE'
   begin
	
   -- DATES in dd/mm/yyyy format, eg May 25th = '25/05/2016'.  Create a YYYYMMDD value
   SELECT @dt_new = right(@dt,4) + substring(@dt,3,2) + LEFT(@dt,2)
   IF ISDATE(@dt_new) = 1
      SET @ret = CAST(@dt_new as DATE)
   ELSE
      SET @ret = NULL
	
   end
ELSE
   begin
   -- DATES in mm/dd/yyyy format, eg May 25th = '05/25/2016'.  Create a YYYYMMDD value
   SELECT @dt_new = right(@dt,4) + LEFT(@dt,2) + substring(@dt,3,2) 
   IF ISDATE(@dt_new) = 1 
      SET @ret = CAST(@dt_new as DATE)
   ELSE
      SET @ret = NULL
	
   end
RETURN @ret
END
GO

Select allOpen in new window

Function calls

SELECT dbo.fnConvertEuropeanDateToUS ('25/05/2016', 'EE') -- good
SELECT dbo.fnConvertEuropeanDateToUS ('05/25/2016', 'EE') -- not good, returns NULL
SELECT dbo.fnConvertEuropeanDateToUS ('25/05/2016', 'US') -- not good, returns NULL
SELECT dbo.fnConvertEuropeanDateToUS ('05/25/2016', 'US') -- good

Select allOpen in new window

Thanks.
Jim