Solved

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

Posted on 2016-10-04
5
65 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 100 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 34

Assisted Solution

by:ste5an
ste5an earned 25 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 69

Accepted Solution

by:
Scott Pletcher earned 375 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 65

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 69

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Question about Common Table Expressions 3 45
get count of orders by customer Sql Server table. 3 48
Datatable / Dates ? 4 33
Delete duplicates from SQL Server table 2 27
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…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

752 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