How to Swap column values in SQL server of different datatype

Question

I have one Table in SQL Server 2008, like T1, and have column likeC1 and C2`

I just want to swap values from C1 to C2 and C2 to C1

for Example

C1              C2
--------        --------
12/11/2014      12/10/2014
12/13/2014      12/12/2014
12/15/2014      12/14/2014
12/17/2014      12/16/2014
12/19/2014      12/18/2014

I need records as follows

C1              C2
--------        --------
12/10/2014      12/11/2014
12/12/2014      12/13/2014
12/14/2014      12/15/2014
12/16/2014      12/17/2014
12/18/2014      12/19/2014

for that I am trying something like below

UPDATE T1 SET C1=C2, C2=C1

EDIT :

I am getting Conversion failed when converting date and/or time from character string. Error. C1 is nvarchar(50) datatype and C2 is DateTime,

I am storing DateTime.Now.Date in C1, But it will depend on Regional Setting of Window, If Date is set as "MM-DD-yyyy" Format then in C1 Value will be in same Format. My main problem is some time User can change its windows setting to any other format so I am getting Said Error.

The answer of How do I swap column values in sql server 2008? this not meet my requirement due to it has same datatypes

I am So Sorry for providing only int Value

Did you get an error when you tried it? What was this error? — Andrew Shepherd, Jan 28 '15 at 04:26
If you get errors, this update may violate constraints etc.. — Ormoz, Jan 28 '15 at 04:28
You will need to either normalize the input on insert to the DB, or keep additional information of the regional settings. — Y.S, Jan 28 '15 at 04:58
Why do you need to swap values? Why can you not just swap column names? — Raj, Jan 28 '15 at 05:37

score 1 · Answer 1 · answered Jan 28 '15 at 04:41

--drop table #TableA
SELECT  *
into #TableA
FROM    (
    select  10 Num1 , 11 Num2
        union
    select  12 Num1 , 13 Num2
        union
    select  14 Num1, 15 Num2
        union
    select  16 Num1 , 17 Num2
        union
    select  18 Num1 , 19 Num2
) a

update #TableA SET Num1 = Num2 , Num2 = Num1

SELECT  *
FROM    #TableA

Ajay2707 · Accepted Answer · 2015-01-28T05:13:23.777

Your query work for you..This is sample and give the perfect result.

declare @t table(c1 int, c2 int)

insert into @t values(10,11),(11,13),(14,15),(16,17),(18,19)
select * from @t
update @t set c1 = c2 ,c2=c1

select * from @t

Updated :- Please check my answer.

You can set dateformat in sqlserver side via "set dateformat mdy"

set dateformat dmy
select isdate( '18/12/2014' ), convert( datetime, '18/12/2014' ,103)

you can use ISDATE with convert date function.

set dateformat mdy
--the problem is in your insert not in update, if data is save in 'mm-dd-yyyy', then no issue with swap.
;with cte as (
    select 
        case when isdate('19/12/2014') = 1
             then '19/12/2014'
             else convert( datetime, '19/12/2014' ,103)
        end stdt
    from @t
)

select * from cte

--update @t set c1 = c2 ,c2=c1
--select * from @t

score 1 · Answer 3 · answered Jan 28 '15 at 04:45

your query will work, if you want another method you can follow the below one

select* from tablename;
Go

DECLARE @tempcol as int
update tablename
set    @tempcol= C1,
       C1= C2,
       C2= @tempcol

(if you have any conditions then ) WHERE condition; GO

select* from tablename;

How to Swap column values in SQL server of different datatype

3 Answers3