Date Formats

Hi All,

is there a script that can tell me the different date formats in a column....supposed not allowed if table correctly set-up....

This is part of a DQ exercise....

Thanks

This will show you the different chars used (but cannot differentiate ex. yyyymmdd from yyyyddmm):

with cte
 as (select datestringfield
           ,row_number() over(partition by replace(
                                           replace(
                                           replace(
                                           replace(
                                           replace(
                                           replace(
                                           replace(
                                           replace(
                                           replace(
                                           replace(datestringfield
                                                  ,'0',' ')
                                                  ,'1',' ')
                                                  ,'2',' ')
                                                  ,'3',' ')
                                                  ,'4',' ')
                                                  ,'5',' ')
                                                  ,'6',' ')
                                                  ,'7',' ')
                                                  ,'8',' ')
                                                  ,'9',' ')
                              order by datestringfield
                             )
            as rn
       from yourtable
     )
select *
  from cte
 where rn=1
;
1 Like

Hi,
I got a sp that you can setup in your server and it will gives you all the date format that you can check in case you forgot for all the formats.

http://www.sqlservercentral.com/blogs/denniss-sql-blog-1/2015/04/16/script-corner-date-format/

Hope this helps

1 Like

Thanks guys that has worked.....