Handling string column with dates

• GrassHopper

  SSCarpal Tunnel

  Points: 4473

  More actions

  October 18, 2017 at 1:56 pm

  #1964280

  Luis Cazares - Wednesday, October 18, 2017 1:38 PM

  GrassHopper - Wednesday, October 18, 2017 8:22 AM

  I imported a csv file that has a column with dates.  The column is a varchar type and it has different format dates(see below).  I created a new colum as datetime.  I want to update the new datetime column with the values in the varchar column.  How can i do this?

  03/15/2017
  14.06.15
  27.01.2014
  24th May 2
  09.09.2015
  42550
  27.07.16

  You need to be aware that you have truncated data. The second date can be converted using format code 2 or 4. The fourth date is missing the 3 last digits of the year. There's no universal start date, so in SQL Server is interpreted as 2016-07-01, while in Excel is interpreted as 2016-06-29.
  You mention that "Source is what it is" and you can't change that. I hope that you can tell your users that their data might be completely wrong.

  That said, here's some code:
  
SELECT *,
  CASE WHEN dateString LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' THEN CONVERT(datetime, dateString, 101)
   WHEN dateString LIKE '[0-9][0-9].[0-9][0-9].[0-9][0-9]' THEN CONVERT(datetime, dateString, 4)
   WHEN dateString LIKE '[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]' THEN CONVERT(datetime, dateString, 104)
   WHEN dateString NOT LIKE '%[^0-9]%' THEN CONVERT( datetime, CONVERT( int, dateString))
   WHEN dateString LIKE '[0-9][0-9][a-z][a-z] [a-z][a-z][a-z] [0-2]' THEN TRY_CONVERT( datetime, STUFF(RIGHT( dateString, 5)+RIGHT(YEAR(GETDATE()),3), 5, 0, LEFT( dateString, 2) + ' '))
   END,
  CASE WHEN dateString LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' THEN CONVERT(datetime, dateString, 101)
   WHEN dateString LIKE '[0-9][0-9].[0-9][0-9].[0-9][0-9]' THEN CONVERT(datetime, dateString, 2)
   WHEN dateString LIKE '[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]' THEN CONVERT(datetime, dateString, 104)
   WHEN dateString NOT LIKE '%[^0-9]%' THEN CONVERT( datetime, CONVERT( int, dateString))
   WHEN dateString LIKE '[0-9][0-9][a-z][a-z] [a-z][a-z][a-z] [0-2]' THEN TRY_CONVERT( datetime, STUFF(RIGHT( dateString, 5)+RIGHT(YEAR(GETDATE()),3), 5, 0, LEFT( dateString, 2) + ' '))
   END
FROM #dates


  starts to show data and before completing, this error comes up:

  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

• GrassHopper

  SSCarpal Tunnel

  Points: 4473

  More actions

  October 18, 2017 at 2:02 pm

  #1964283

  DesNorton - Wednesday, October 18, 2017 1:24 PM

  You could also try testing for specific formats
  CREATE TABLE #dates (dateString varchar(10));
  GO
  INSERT INTO #dates
  VALUES
  ('03/15/2017'),
  ('04/18/17'),
  ('14.06.15'),
  ('27.01.2014'),
  ('24th May 2'),
  ('09.09.2015'),
  ('42550'),
  ('27.07.16');
  GO

  SELECT dateString
  , ConvDate = CASE
        -- Assume that 42550 is a SQL, 0 based date number
        WHEN dateString NOT LIKE '%[^0-9]%'           THEN DATEADD(DAY, CONVERT(int,dateString),0) -- 42550
        -- The dates with a "." appear to be DD.MM.YY[YY] format
        WHEN dateString LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' THEN CONVERT(date, dateString, 104)    -- 27.01.2014
        WHEN dateString LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]'    THEN CONVERT(date, dateString, 4)    -- 27.07.16
        -- The dates with a "'" appear to be MM/DD/YY[YY] format
        WHEN dateString LIKE '[0-1][0-9]/[0-3][0-9]/[0-9][0-9][0-9][0-9]' THEN CONVERT(date, dateString, 101)    -- 03/15/2017
        WHEN dateString LIKE '[0-1][0-9]/[0-3][0-9]/[0-9][0-9]'    THEN CONVERT(date, dateString, 1)    -- 04/18/17
        -- Let SQL try to determine which format.
        WHEN ISDATE(dateString) = 1              THEN CONVERT(date, dateString)
       END
  FROM #dates;

  GO
DROP TABLE #dates;


  Starts to work and then:  

  Conversion failed when converting date and/or time from character string.

• Thom A

  SSC Guru

  Points: 99130

  More actions

  October 18, 2017 at 2:06 pm

  #1964284

  DesNorton - Wednesday, October 18, 2017 1:04 PM

  GrassHopper - Wednesday, October 18, 2017 12:54 PM

  TRY_CONVERT is a built in SQL function ? if so, what version?  I'm using 2008, but I have access to SQL 2016 SQL studio.  The db is a 2008 format.

  TRY_CONVERT was introduced in SQL 2012

  Sorry, I missed that this was 2008.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 18, 2017 at 2:10 pm

  #1964286

  GrassHopper - Wednesday, October 18, 2017 1:56 PM

  starts to show data and before completing, this error comes up:

  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

  That only means that you have inconsistent date formats. Here's a different option that would convert most, but will certainly get dates wrong:
  
SELECT *,
  COALESCE( TRY_CONVERT( datetime, dateString, 100)
         ,TRY_CONVERT( datetime, dateString, 101)
         ,TRY_CONVERT( datetime, dateString, 102)
         ,TRY_CONVERT( datetime, dateString, 103)
         ,TRY_CONVERT( datetime, dateString, 104)
         ,TRY_CONVERT( datetime, dateString, 105)
         ,TRY_CONVERT( datetime, dateString, 106)
         ,TRY_CONVERT( datetime, dateString, 108)
         ,TRY_CONVERT( datetime, dateString, 109)
         ,TRY_CONVERT( datetime, dateString, 110)
         ,TRY_CONVERT( datetime, dateString, 111)
         ,TRY_CONVERT( datetime, dateString, 112)
         ,TRY_CONVERT( datetime, dateString, 00)
         ,TRY_CONVERT( datetime, dateString, 01)
         ,TRY_CONVERT( datetime, dateString, 02)
         ,TRY_CONVERT( datetime, dateString, 03)
         ,TRY_CONVERT( datetime, dateString, 04)
         ,TRY_CONVERT( datetime, dateString, 05)
         ,TRY_CONVERT( datetime, dateString, 06)
         ,TRY_CONVERT( datetime, dateString, 08)
         ,TRY_CONVERT( datetime, dateString, 09)
         ,TRY_CONVERT( datetime, dateString, 10)
         ,TRY_CONVERT( datetime, dateString, 11)
         ,TRY_CONVERT( datetime, dateString, 12)
         ,TRY_CONVERT( datetime, TRY_CONVERT( int, dateString)))
FROM #dates


  Forget about this, I just saw that you're working on a 2008 database.
  

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• GrassHopper

  SSCarpal Tunnel

  Points: 4473

  More actions

  October 18, 2017 at 2:20 pm

  #1964290

  Ok, so i was able to get past the hurdle.  It worked for me.  Thanks everyone!

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 18, 2017 at 2:24 pm

  #1964291

  Here's another possible solution, pure brute force.
  
WITH
E(n) AS(
  SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
  SELECT a.n FROM E a, E b
),
E4(n) AS(
  SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
  SELECT TOP(8000) DATEADD(dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, '2000') n
  FROM E4
)
SELECT n,
  CONVERT( char(10), CONVERT(int, n)) AS format_Int,
  CONVERT(char(10), n, 00) AS format_00 ,
  CONVERT(char(10), n, 01) AS format_01 ,
  CONVERT(char(10), n, 02) AS format_02 ,
  CONVERT(char(10), n, 03) AS format_03 ,
  CONVERT(char(10), n, 04) AS format_04 ,
  CONVERT(char(10), n, 05) AS format_05 ,
  CONVERT(char(10), n, 06) AS format_06 ,
  CONVERT(char(10), n, 07) AS format_07 ,
  CONVERT(char(10), n, 08) AS format_08 ,
  CONVERT(char(10), n, 09) AS format_09 ,
  CONVERT(char(10), n, 10) AS format_10 ,
  CONVERT(char(10), n, 11) AS format_11 ,
  CONVERT(char(10), n, 12) AS format_12 ,
  CONVERT(char(10), n, 100) AS format_100 ,
  CONVERT(char(10), n, 101) AS format_101 ,
  CONVERT(char(10), n, 102) AS format_102 ,
  CONVERT(char(10), n, 103) AS format_103 ,
  CONVERT(char(10), n, 104) AS format_104 ,
  CONVERT(char(10), n, 105) AS format_105 ,
  CONVERT(char(10), n, 106) AS format_106 ,
  CONVERT(char(10), n, 107) AS format_107 ,
  CONVERT(char(10), n, 108) AS format_108 ,
  CONVERT(char(10), n, 109) AS format_109 ,
  CONVERT(char(10), n, 110) AS format_110 ,
  CONVERT(char(10), n, 111) AS format_111 ,
  CONVERT(char(10), n, 112) AS format_112
INTO #AllDateFormats
FROM cteTally

  SELECT d.dateString, a.n
FROM #dates d
JOIN #AllDateFormats a ON d.dateString IN (format_int, format_00 ,format_01 ,format_02 ,format_03 ,format_04 ,format_05 ,format_06 ,
               format_07 ,format_08 ,format_09 ,format_10 ,format_11 ,format_12 ,format_100 ,format_101 ,
               format_102 ,format_103 ,format_104 ,format_105 ,format_106 ,format_107 ,format_108 ,format_109 ,
               format_110 ,format_111 ,format_112)


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• GrassHopper

  SSCarpal Tunnel

  Points: 4473

  More actions

  October 19, 2017 at 10:04 am

  #1964372

  DesNorton - Wednesday, October 18, 2017 1:24 PM

  You could also try testing for specific formats
  CREATE TABLE #dates (dateString varchar(10));
  GO
  INSERT INTO #dates
  VALUES
  ('03/15/2017'),
  ('04/18/17'),
  ('14.06.15'),
  ('27.01.2014'),
  ('24th May 2'),
  ('09.09.2015'),
  ('42550'),
  ('27.07.16');
  GO

  SELECT dateString
  , ConvDate = CASE
        -- Assume that 42550 is a SQL, 0 based date number
        WHEN dateString NOT LIKE '%[^0-9]%'           THEN DATEADD(DAY, CONVERT(int,dateString),0) -- 42550
        -- The dates with a "." appear to be DD.MM.YY[YY] format
        WHEN dateString LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' THEN CONVERT(date, dateString, 104)    -- 27.01.2014
        WHEN dateString LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]'    THEN CONVERT(date, dateString, 4)    -- 27.07.16
        -- The dates with a "'" appear to be MM/DD/YY[YY] format
        WHEN dateString LIKE '[0-1][0-9]/[0-3][0-9]/[0-9][0-9][0-9][0-9]' THEN CONVERT(date, dateString, 101)    -- 03/15/2017
        WHEN dateString LIKE '[0-1][0-9]/[0-3][0-9]/[0-9][0-9]'    THEN CONVERT(date, dateString, 1)    -- 04/18/17
        -- Let SQL try to determine which format.
        WHEN ISDATE(dateString) = 1              THEN CONVERT(date, dateString)
       END
  FROM #dates;

  GO
DROP TABLE #dates;


  
I used your code in parts.  I am getting error msg's when trying to update the dates with dots. i.e. 21.03.17  
error msg:
Conversion failed when converting date and/or time from character string.

  Code I am using to convert t

  Select PTL_StartDate, PTL_EndDate
    , Case WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216') THEN CONVERT(date, PTL_StartDate, 104)  -- 27.01.2014
         WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]' AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32') THEN CONVERT(date, PTL_StartDate, 4)  -- 27.07.16
  Else '01/01/1900'
  END as startdate_2
FROM XLSHdr_Promo_TescoUK
Where (PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' OR PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]')
AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216') AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32')


• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  October 19, 2017 at 11:19 am

  #1964383

  GrassHopper - Thursday, October 19, 2017 10:04 AM

  I used your code in parts.  I am getting error msg's when trying to update the dates with dots. i.e. 21.03.17  
  error msg:
  Conversion failed when converting date and/or time from character string.

  Code I am using to convert t

  Select PTL_StartDate, PTL_EndDate
      , Case WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216') THEN CONVERT(date, PTL_StartDate, 104)  -- 27.01.2014
           WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]' AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32') THEN CONVERT(date, PTL_StartDate, 4)  -- 27.07.16
    Else '01/01/1900'
    END as startdate_2
  FROM XLSHdr_Promo_TescoUK
  Where (PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' OR PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]')
  AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216') AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32')

  I suspect that SQL is attempting to convert all the underlying data before filtering the results.
  Try something like this
  SELECT PTL_StartDate, PTL_EndDate
INTO #Staging
FROM XLSHdr_Promo_TescoUK
WHERE (PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' OR PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]')
AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216')
AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32');

  Select PTL_StartDate, PTL_EndDate
, CASE WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216') THEN CONVERT(date, PTL_StartDate, 104) -- 27.01.2014
   WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]' AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32') THEN CONVERT(date, PTL_StartDate, 4) -- 27.07.16
   ELSE CONVERT(date, '01/01/1900', 101)
  END as startdate_2
FROM #Staging;

  ---

  How to post data/code on a forum to get the best help.

• SQLPirate

  SSCrazy

  Points: 2880

  More actions

  October 19, 2017 at 11:23 am

  #1964385

  GrassHopper - Thursday, October 19, 2017 10:04 AM

  
  I used your code in parts.  I am getting error msg's when trying to update the dates with dots. i.e. 21.03.17  
  error msg:
  Conversion failed when converting date and/or time from character string.

  Code I am using to convert t

  Select PTL_StartDate, PTL_EndDate
      , Case WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216') THEN CONVERT(date, PTL_StartDate, 104)  -- 27.01.2014
           WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]' AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32') THEN CONVERT(date, PTL_StartDate, 4)  -- 27.07.16
    Else '01/01/1900'
    END as startdate_2
  FROM XLSHdr_Promo_TescoUK
  Where (PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' OR PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]')
  AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216') AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32')

  Do you know what the value is of the record you're changing? 
  If a record is in DD.MM.YY or DD.MM.YYYY format the convert to date 4 or 104, respectively, will work. But if your record is MM.DD.YY or MM.DD.YYYY it will fail.

• GrassHopper

  SSCarpal Tunnel

  Points: 4473

  More actions

  October 19, 2017 at 11:57 am

  #1964394

  DesNorton - Thursday, October 19, 2017 11:19 AM

  GrassHopper - Thursday, October 19, 2017 10:04 AM

  I used your code in parts.  I am getting error msg's when trying to update the dates with dots. i.e. 21.03.17  
  error msg:
  Conversion failed when converting date and/or time from character string.

  Code I am using to convert t

  Select PTL_StartDate, PTL_EndDate
      , Case WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216') THEN CONVERT(date, PTL_StartDate, 104)  -- 27.01.2014
           WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]' AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32') THEN CONVERT(date, PTL_StartDate, 4)  -- 27.07.16
    Else '01/01/1900'
    END as startdate_2
  FROM XLSHdr_Promo_TescoUK
  Where (PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' OR PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]')
  AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216') AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32')

  I suspect that SQL is attempting to convert all the underlying data before filtering the results.
  Try something like this
  SELECT PTL_StartDate, PTL_EndDate
INTO #Staging
FROM XLSHdr_Promo_TescoUK
WHERE (PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' OR PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]')
AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216')
AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32');

  Select PTL_StartDate, PTL_EndDate
, CASE WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216') THEN CONVERT(date, PTL_StartDate, 104) -- 27.01.2014
   WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]' AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32') THEN CONVERT(date, PTL_StartDate, 4) -- 27.07.16
   ELSE CONVERT(date, '01/01/1900', 101)
  END as startdate_2
FROM #Staging;

  This is the message I got :

  (124520 row(s) affected)
  Msg 241, Level 16, State 1, Line 9
  Conversion failed when converting date and/or time from character string.

• GrassHopper

  SSCarpal Tunnel

  Points: 4473

  More actions

  October 19, 2017 at 12:44 pm

  #1964407

  I think SQL Pirate is right, there could be some records with dates that have mmddyyyy instead of ddmmyyyy and this is causing the conversion failure error.  I would have to check for the month being greater than 12  or exclude those for now?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 19, 2017 at 12:48 pm

  #1964408

  This is way it was suggested to go back to the source of the file and have them fix the dates, unfortunately you already shot that down saying it is what it is.

  You will need to identify all the unique date formats in that column.  Pull each of the unique dates by each format into a temporary table and convert those dates.  Some you will be able to convert directly using the CONVERT function with the appropriate date format code.  Those are the ones I would do first.  The rest you will have to manipulate into a format that can be converted using CONVERT and a date format code.  Those will be the fun ones when you have dates like 10.11.12.  What is that date, 20121011, 20101112, ???.  You will have to make a decision.

• GrassHopper

  SSCarpal Tunnel

  Points: 4473

  More actions

  October 19, 2017 at 12:48 pm

  #1964409

  DesNorton, 
  I excluded the month > 12 and the error msg is gone.

  SELECT PTL_StartDate, PTL_EndDate
  INTO #Staging
  FROM XLSHdr_Promo_TescoUK
  WHERE (PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' OR PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]')
  AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216')
  AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32')
  AND PARSENAME(REPLACE(PTL_StartDate, '/', '.'),2) < 13;

  Select PTL_StartDate, PTL_EndDate
  , CASE WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216') THEN CONVERT(date, PTL_StartDate, 104) -- 27.01.2014
   WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]' AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32') THEN CONVERT(date, PTL_StartDate, 4) -- 27.07.16
   ELSE CONVERT(date, '01/01/1900', 101)
  END as startdate_2
  FROM #Staging;

• SQLPirate

  SSCrazy

  Points: 2880

  More actions

  October 19, 2017 at 1:18 pm

  #1964415

  You could swap the values in those MM.DD.YY ones with something like

     case when PARSENAME(REPLACE(PTL_StartDate, '/', '.'),2) > 13
        then substring(PTL_StartDate,4,2) + '.' + substring(PTL_StartDate,1,2) + '.' + substring(PTL_StartDate,7,2)
        else PTL_StartDate
        end

  Quick test took my PTL_StartDate values from

  11.25.17
  01.15.16
  11.12.17

  to

  25.11.17
  15.01.16
  11.12.17
  which can then be converted with date 4.

  My concern would be ambiguous columns will just be accepted...
  10.09.17 is totally valid as both October 9th, 2017 AND September 10th, 2017 and you really don't have a way to suss out what it should be.

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  October 19, 2017 at 1:41 pm

  #1964418

  Where there are cases of DD.MM.YYYY mixed with MM.DD.YYYY, something like this should do the trick
  select PTL_StartDate
, YearPart = PARSENAME(PTL_StartDate, 1)
, MonthPart = CASE WHEN PARSENAME(PTL_StartDate, 2) > 12 THEN PARSENAME(PTL_StartDate, 3) ELSE PARSENAME(PTL_StartDate, 2) END
, DayPart = CASE WHEN PARSENAME(PTL_StartDate, 2) > 12 THEN PARSENAME(PTL_StartDate, 2) ELSE PARSENAME(PTL_StartDate, 3) END
, NewDate = CONVERT(date, CASE WHEN PARSENAME(PTL_StartDate, 2) > 12 THEN PARSENAME(PTL_StartDate, 2) ELSE PARSENAME(PTL_StartDate, 3) END + '.'
         + CASE WHEN PARSENAME(PTL_StartDate, 2) > 12 THEN PARSENAME(PTL_StartDate, 3) ELSE PARSENAME(PTL_StartDate, 2) END + '.'
         + PARSENAME(PTL_StartDate, 1)
      , 104) -- DD.MM.YYYY


  ---

  How to post data/code on a forum to get the best help.