Solved

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

Posted on 2016-10-04
5
27 Views
Last Modified: 2016-10-04
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
Comment
Question by:Jim Horn
5 Comments
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 100 total points
Comment Utility
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
 
LVL 32

Assisted Solution

by:Stefan Hoffmann
Stefan Hoffmann earned 25 total points
Comment Utility
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 375 total points
Comment Utility
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
 
LVL 65

Author Comment

by:Jim Horn
Comment Utility
>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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now