Date Formats

Transact-SQL
1

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

2

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
3

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
4

Thanks guys that has worked.....