Updating string values in a table to be flopped ar

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
                                             )
                          )

Answer 2

The most straight forward way would be to convert the string to a date, and then back out in the desired format within an update statement.

Unfortunately I cannot run any code at this time, so any example code I could give would probably be unhelpful, but hopefully these links will assist you. It may be necessary to perform direct string manipulation as I notice that sql server 2008 appears to lack a "date mask" style of conversion. http://msdn.microsoft.com/en-us/library/ms180878(v=sql.100).aspx http://msdn.microsoft.com/en-us/library/ms187928.aspx

Note that this is the EXACT reason you would typically store a date as a date object. It's quite straight forward to create an output string from a date in a format you desire in a select statement, but more convoluted to do so when it is stored as raw characters.

Sorry this answer isn't better; my primary experience is in Oracle, but hopefully this gets you started.