SQL Server 2008 : issues with date conversion

Question

I'm currently updating our database system in which the date field is in nvarchar (20), however I have changed this into smalldatetime. In doing so though, certain dates are changing from 11/12/2014-------> 12/11/2014

Old Date  ------------------------ New Date
11/12/2014------------------------ 2014-11-12 00:00:00.000
12 December 2014------------------ 2014-12-12 00:00:00.000
15/12/2014------------------------ 2014-12-15 00:00:00.000
15/12/2014------------------------ 2014-12-15 00:00:00.000
15 December 2014------------------ 2014-12-15 00:00:00.000
15/12/2014------------------------ 2014-12-15 00:00:00.000
15/12/2014------------------------ 2014-12-15 00:00:00.000
15/12/2014------------------------ 2014-12-15 00:00:00.000
16 December 2014------------------ 2014-12-16 00:00:00.000
16 December 2014------------------ 2014-12-16 00:00:00.000

The following is the code which i am using;

WITH CreateDt1
AS
(
SELECT '14 DEC 12' AS OriginalDate
UNION ALL
SELECT '13/10/2005'
UNION ALL
SELECT '12/14/2012'
UNION ALL
SELECT '24/04/2006 17:17:19' 
UNION ALL
SELECT '28/02/2011'
)
SELECT CreateDt1,
CASE WHEN ISDATE(CreateDt1) = 1
    THEN CAST(CreateDt1 AS datetime)
    ELSE 
        CASE WHEN SUBSTRING(CreateDt1, 3, 1) = '/'
        THEN 
            CASE WHEN ISDATE(SUBSTRING(CreateDt1, 4, 2) + '/' + LEFT(CreateDt1, 2) + '/' + RIGHT (CreateDt1, 4)) = 1
                THEN CAST(SUBSTRING(CreateDt1, 4, 2) + '/' + LEFT(CreateDt1 , 2) + '/' + RIGHT (CreateDt1, 4) AS datetime)
                WHEN ISDATE(SUBSTRING(CreateDt1, 4, 2) + '/' + LEFT(CreateDt1, 2) + '/' + RIGHT (LEFT(CreateDt1,10), 4)) = 1
                THEN CAST(SUBSTRING(CreateDt1, 4, 2) + '/' + LEFT(CreateDt1 , 2) + '/' + RIGHT (LEFT(CreateDt1,10), 4) AS datetime)
            END

        END
    END AS NewDate
FROM fct_Project;

Is there any way in which this can altered?

As they are in alphabetical order so for example 12 December 2014 and 31/12/2014 are changing as normal its the dates which are like 11/12/2014 were the issue is! — UCDA2919, Mar 11 '19 at 08:51
Add some more sample table data and also the expected result. All as formatted text, not images! — jarlh, Mar 11 '19 at 08:52
Do you mean you don't know if 11/12/2014 means 2014-11-12, or if it means 2014-12-11? (YYYY-MM-DD.) — jarlh, Mar 11 '19 at 08:53
I have added some sample data with old data on left and new on the right with the data in alphabetical order so the issue can be seen with the first date — UCDA2919, Mar 11 '19 at 09:02
So, to be clear. Are you stating that, for all dates of the form `NN'/'NN'/'NNNN` where all of the `N`s are digits, that if both of the first two values are less than 13, it's swapping those days and months, but for all other conversions things are right? — Damien_The_Unbeliever, Mar 11 '19 at 09:12
@UCDA2919 . . . I get an error when I try the conversion. You should edit your question and show the code you are using. — Gordon Linoff, Mar 11 '19 at 11:15
You can't do this in one query. It is good that you realize that varchar was a poor choice for storing this information. But now you first must cleanse your data before you attempt conversion. Your small sample data clearly shows different formats and ambiguous values. Does "14 Dec 12" represent 2014-12-12 or 2012-12-14? Your 2nd row is obviously ddmm while the 3rd is mmdd. Surely there will be more problems with existing values. This will be a painful lesson. — SMor, Mar 11 '19 at 12:31
@SMor it represents as 2014-12-12! Yes it is painful. Any tips on cleansing the data. — UCDA2919, Mar 11 '19 at 12:34
Your second example makes your first example unclear, which makes the whole question unclear. How do you know that `11/12/2014` is being incorrectly converted if your database has inconsistent formats? How do you know the original intent of the entry? How do you know `2014-11-12` isn't the correct conversion? — Tab Alleman, Mar 11 '19 at 13:06

score 0 · Answer 1 · answered Mar 11 '19 at 15:44

Get your data consistent, then apply casting/conversions.

DECLARE @dates TABLE (
    DateString VARCHAR(50)
    ,[Date] DATE
)
INSERT INTO @dates (DateString) SELECT '11/12/2014'
INSERT INTO @dates (DateString) SELECT '12 December 2014'
INSERT INTO @dates (DateString) SELECT '15/12/2014'


UPDATE @dates
SET [Date]=
    CONVERT(DATE,
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DateString,
            ' January ','/01/'),
            ' February ','/02/'),
            ' March ','/03/'),
            ' April ','/04/'),
            ' May ','/05/'),
            ' June ','/06/'),
            ' July ','/07/'),
            ' August ','/08/'),
            ' September ','/09/'),
            ' October ','/10/'),
            ' November ','/11/'),
            ' December ','/12/')
    ,103)

SELECT * FROM @dates

SQL Server 2008 : issues with date conversion

1 Answers1