How to unify the datetime format when in Varchar

Vote count: 0

I would like to normalize my Datetime for a given database. I need to be able to use the date to do statistics. Here is what I've done so far:

select
 CASE PATINDEX('%[0-9]/[0-9]/[0-9][0-9][0-9][0-9] [0-9]:[0-9][0-9]:[0-9][0-9]%',[Last Updated])
    WHEN 1 then 'Pattern1'--CAST([Last Updated] AS Datetime) --2/8/2017 2:30:14 PM 
    ELSE
     CASE PATINDEX('%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]%',[Last Updated])
        WHEN 1 then 'Pattern2' --2015-03-02 03:46:38 PM
        ELSE
        CASE PATINDEX('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]%',[Last Updated])
            WHEN 1 THEN 'Pattern3'
            ELSE
            CASE PATINDEX('%[0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]%',[Last Updated])
                WHEN 1 THEN 'Pattern4'
                ELSE
                CASE PATINDEX('%[0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] [0-9]:[0-9][0-9]:[0-9][0-9]%',[Last Updated])
                    WHEN 1 THEN 'Pattern5'
                    ELSE
                    CASE PATINDEX('%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9]:[0-9][0-9]:[0-9][0-9]%',[Last Updated])
                        WHEN 1 THEN 'Pattern6'
                        ELSE
                        CASE PATINDEX('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] [0-9]:[0-9][0-9]:[0-9][0-9]%',[Last Updated])
                            WHEN 1 THEN 'Pattern7'
                            ELSE
                            CASE PATINDEX('%[0-9]/[0-9]/[0-9][0-9][0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]%',[Last Updated])
                                WHEN 1 THEN 'Pattern8'
                                ELSE
                                CASE PATINDEX('%[0-9][0-9]/[0-9]/[0-9][0-9][0-9][0-9] [0-9]:[0-9][0-9]:[0-9][0-9]%',[Last Updated])
                                    WHEN 1 THEN 'Pattern9'
                                    ELSE
                                    CASE PATINDEX('%[0-9][0-9]/[0-9]/[0-9][0-9][0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]%',[Last Updated])
                                        WHEN 1 THEN 'Pattern10'






END
    END
        END
            END
                END
                    END
                        END
                            END
                                END
                                    END
as 'Pattern'

When i am trying to cast as datetime, it gives me an error... Here are some examples:

Pattern1: 2/8/2017 8:06:56 AM 2/2/2017 2:42:09 PM 1/3/2017 9:10:20 AM

Pattern 2: 2016-12-20 11:08:20 2016-11-09 10:04:35 2016-11-01 10:53:11 AM 2017-02-03 09:13:14 2016-11-09 10:09:09

Pattern3: 12/14/2016 11:54:53 AM 12/16/2016 11:05:24 AM 12/19/2016 12:23:51 PM

Pattern4: 8/15/2016 12:13:35 PM 4/17/2015 12:29:54 PM 2/22/2016 10:44:11 AM 6/12/2014 10:08:07 AM 9/16/2013 12:18:22 PM

I would like them to all be normalized to the format: '02/13/2017 11:58:00'

Is there any way of doing this??

asked 20 secs ago

InfiniteLoop

Let's block ads! (Why?)

How to unify the datetime format when in Varchar