我想把日期字段的年份全部改为2010年,不知道怎么改,请指点,我转化成VARCHAR时,怎么年份排到中间去了xsrq
2085-05-01 18:35:53.000
2099-05-01 18:35:53.000
2088-05-01 18:35:53.000
2010-05-01 18:35:53.000
改为
xsrq
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000
2085-05-01 18:35:53.000
2099-05-01 18:35:53.000
2088-05-01 18:35:53.000
2010-05-01 18:35:53.000
改为
xsrq
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-05-04 18:27:34
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([xsrq] datetime)
insert [tb]
select '2085-05-01 18:35:53.000' union all
select '2099-05-01 18:35:53.000' union all
select '2088-05-01 18:35:53.000' union all
select '2010-05-01 18:35:53.000'
--------------开始查询--------------------------
update tb set [xsrq]='2010-05-01 18:35:53.000'select * from tb
----------------结果----------------------------
/*xsrq
-----------------------
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000(4 行受影响)
*/
update dj_qtxsmx set datepart(year,xsrq)='2010' where XSRQ > '2010-12-1'
set xsrq=dateadd(yy,2010-year(xsrq),xsrq)
where year(xsrq)!=2010
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-05-04 18:27:34
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([xsrq] datetime)
insert [tb]
select '2075-05-01 18:35:53.000' union all
select '2069-05-01 18:35:53.000' union all
select '2068-05-01 18:35:53.000' union all
select '2010-05-01 18:35:53.000'
--------------开始查询--------------------------
update
tb
set
xsrq=dateadd(yy,2010-year(xsrq),xsrq)
where
year(xsrq)!=2010select * from tb
----------------结果----------------------------
/*xsrq
-----------------------
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000(4 行受影响)
*/
-- Author : htl258(Tony)
-- Date : 2010-05-04 19:01:44
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([xsrq] [datetime])
INSERT INTO [tb]
SELECT '2085-05-01 18:35:53.000' UNION ALL
SELECT '2099-05-01 18:35:53.000' UNION ALL
SELECT '2088-05-01 18:35:53.000' UNION ALL
SELECT '2010-05-01 18:35:53.000'--SELECT * FROM [tb]-->SQL查询如下:
UPDATE TB SET XSRQ='2010'+STUFF(CONVERT(VARCHAR,[XSRQ],120),1,4,'')SELECT * FROM TB
/*
xsrq
-----------------------
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000
2010-05-01 18:35:53.000(4 行受影响)
*/