Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

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

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 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