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.
Function calls
Thanks.
Jim
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
GOFunction 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') -- goodThanks.
Jim
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
I was hoping to add a date validation component to it
If you're in a version of SQL in which TRY_PARSE is available (SQL 2012+), then it's easy, and I won't bother to do sample code for that.
Without TRY_PARSE:
SELECT *
FROM (
VALUES('02/29/2015', 'US'),('02/29/2016', 'US'),('03/31/2015','US'),
) AS sample_dates(dt, CountryCode)
CROSS APPLY dbo.fnConvertEuropeanDateT
ALTER FUNCTION dbo.fnConvertEuropeanDateT
(
@dt varchar(10),
@CountryCode char(2)
)
RETURNS TABLE
AS
RETURN (
SELECT year + month + day AS DateYMD,
CASE WHEN year NOT LIKE '[2][01][0-9][0-9]' OR
month NOT IN ('01','02','03','04','05',
THEN 0
WHEN day NOT LIKE '[0123][0-9]'
THEN 0
WHEN CAST(day AS tinyint) = 0
THEN 0
WHEN month = '02' AND CAST(year AS smallint) % 4 = 0 AND CAST(day AS tinyint) <= 29 THEN 1
WHEN CAST(day AS tinyint) > SUBSTRING('_31283130313031
ELSE 1 END AS IsValidDate
FROM (
SELECT CAST(CASE WHEN @CountryCode IN ('AU', 'DK', 'EE')
THEN 0 ELSE 1 END AS bit) AS Is_US_Format
) AS assign_alias_names1
CROSS APPLY (
SELECT RIGHT(@dt, 4) AS year,
CASE WHEN Is_US_Format = 1 THEN LEFT(@dt, 2) ELSE SUBSTRING(@dt, 4, 2) END AS month,
CASE WHEN Is_US_Format = 1 THEN SUBSTRING(@dt, 4, 2) ELSE LEFT(@dt, 2) END AS day
) AS assign_alias_names2
)
GO
P.S. Yes, I know this code has a bug in the year 2400, 2800, etc. :-).
ASKER
That is the case here, as each file has one CountryCode, and all values for a given CountryCode are either dd/mm/yyyy or mm/dd/yyyy. Codes 'AU', 'DK', 'EE' are dd/mm/yyyy, all others are mm/dd/yyyy.
> select convert(varchar(10), CONVERT(datetime, @dt, 105), 101)
>For best performance, I'd use an inline-table-valued function.
Thanks. These meet my needs. I was hoping to add a date validation component to it, but I can always redirect the row in the SSIS package so that's fine.