sql - Updating string values in a table to be flopped around such as YYYYMMDD string to MMDDYYYY string

Question

I have a table setup for example as

CREATE TABLE Test_Table ( ID int, DATES varchar(8) );

and my table looks like this when selecting it

|   ID    |    Dates   |
------------------------
|    1    |  20140911  |
|    2    |  20140101  |
|    3    |  20140829  |

I'm trying to update my table so that the string value is MMDDYYYY instead of YYYYMMDD For example this is what I'm trying to achieve:

|   ID    |    Dates   |
------------------------
|    1    |  09112014  |
|    2    |  01012014  |
|    3    |  08292014  |

NOTE: I'm not trying to select the table or change the value into datetime. All I'm trying to do is flop the numbers around and update my table if that is even possible.

Answer 1

Assuming our date column is char or varchar, you could do something like this

update foo
set dates = right(dates,4) + left(dates,4)

Or this

update foo
set dates = replace( convert(varchar,convert(datetime,dates,112),110) , '-' , '' )

If all you want to do is display your text differently, the easiest way is

select ... ,
       dates = right(dates,4) + left(dates,4)
from foo

Or create a view and use that instead of the original table:

create view foo_view
as select id ,
          dates = right(dates,4) + left(dates,4)
   from foo

But if you use actual date/time data types, users of your data will get them mapped to suitable date/time types in the client and can then choose how best to display them for their needs.

The other advantage of using date/time types is that they enforce data integrity. Wait until somebody adds or changes a date to make it invalid — say, `20142331'. Then, when you need to display that data in a form with a month name ('Jan 22, 2014, say), hilarity will ensue when you get an exception trying to map the month number to a month name.

If you're not going to use a date/time type, then store year, month and day individually as integer values with suitable check constraints to enforce integrity:

create table foo
(
  id   int not null identity(1,1) primary key ,
  yyyy int not null check ( yyyy between 1900 and 2100 ) ,
  mm   int not null check ( mm between 1 and 12 ) ,
  dd   int not null check ( dd between 1 and ( case mm
                                                 when  4 then 30
                                                 when  6 then 30
                                                 when  9 then 30
                                                 when 11 then 30
                                                 when  2 then case
                                                                when yyyy % 400 = 0 then 29
                                                                when yyyy % 100 = 0 then 28
                                                                when yyyy % 4   = 0 then 29
                                                                else                     28
                                                              end
                                                 else 31
                                               end
                                             )
                          )