Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-10-04
5
Medium Priority
?
146 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 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 400 total points
ID: 41828520
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 36

Assisted Solution

by:ste5an
ste5an earned 100 total points
ID: 41828552
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 70

Accepted Solution

by:
Scott Pletcher earned 1500 total points
ID: 41828687
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 66

Author Comment

by:Jim Horn
ID: 41828703
>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 70

Expert Comment

by:Scott Pletcher
ID: 41828777
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

885 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