Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

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

Open 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

Open in new window


Thanks.
Jim
0
Jim Horn
Asked:
Jim Horn
3 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Declare @dt varchar(10) = '25-05-2016'
 select convert(varchar(10), CONVERT(datetime, @dt, 105), 101) -- this can be casted back to date time if needed.
0
 
ste5anSenior DeveloperCommented:
Use CONVERT with the appropriate format as Kyle wrote:

dd/mm/yyyy is 103
mm/dd/yyyy is 101

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

Open in new window

I don't understand this sample.

Parsing strings to dates means that you must know the used format in advance, cause otherwise you cannot tell what date 01/02/2016 is (2nd of January or 1st of February). Thus the cases returning null cannot happen as long as the data is valid.
0
 
Scott PletcherSenior DBACommented:
Given that the value is already char, I'd use char manipulation only.

For best performance, I'd use an inline-table-valued function.  Naturally you then use CROSS|OUTER APPLY to invoke the function:


CREATE FUNCTION dbo.fnConvertEuropeanDateToUS
(
    @dt varchar(10),
    @CountryCode char(2)
)
RETURNS TABLE
AS
RETURN (
    SELECT CASE
        WHEN @CountryCode IN ('AU', 'DK', 'EE')
        THEN RIGHT(@dt, 4) + SUBSTRING(@dt, 4, 2) + LEFT(@dt, 2)
        ELSE RIGHT(@dt, 4) + LEFT(@dt, 2) + SUBSTRING(@dt, 4, 2)
        END AS DateUS
)
GO
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
>Parsing strings to dates means that you must know the used format in advance,
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.
0
 
Scott PletcherSenior DBACommented:
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'),('03/3a/2012','US')
) AS sample_dates(dt, CountryCode)
CROSS APPLY dbo.fnConvertEuropeanDateToUS(dt, CountryCode)


ALTER FUNCTION dbo.fnConvertEuropeanDateToUS
(
    @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','06','07','08','09','10','11','12')
             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('_312831303130313130313031', CAST(month AS int) * 2, 2) THEN 0
             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. :-).
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now