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