仅更新sql server 2000中日期时间的日期部分

发布于 2024-09-03 21:48:33 字数 511 浏览 2 评论 0原文

我的表中有如下数据。

col1                   col2                   col3
--------------------------------------------------------
6/5/2010 18:05:00   6/2/2010 10:05:00         Null
6/8/2010 15:05:00   6/3/2010 10:45:00       6/5/2010 11:05:00 
6/3/2010 15:05:00   Null                    6/7/2010 12:05:00 
6/1/2010 15:05:00   6/3/2010 10:45:00       6/1/2010 14:05:00

我的要求是我想用单个日期更新这些列的日期而不干扰时间。举例来说，我想用 2010 年 6 月 1 日更新表数据，其中字段数据不为空。请让我知道更新表数据的查询。

谢谢&问候，

穆拉里

原文

I have data in the table like the following.

col1                   col2                   col3
--------------------------------------------------------
6/5/2010 18:05:00   6/2/2010 10:05:00         Null
6/8/2010 15:05:00   6/3/2010 10:45:00       6/5/2010 11:05:00 
6/3/2010 15:05:00   Null                    6/7/2010 12:05:00 
6/1/2010 15:05:00   6/3/2010 10:45:00       6/1/2010 14:05:00

what my requirement is I want to update the date of there columns with single date without disturbing the time. say for example I want to update the table data with 6/1/2010 where the field data is not null. please let me know the query for updating the table data.

thanks & regards,

murali

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一紙繁鸢 2024-09-10 21:48:33

我认为这应该对你有用。

create table #t
(
col1 datetime
)

Insert Into #t 
values ('2010-06-01 10:00:00')

Insert Into #t 
values ('2010-06-06 11:00:00')

Insert Into #t 
values ('2010-05-24 12:40:00')

Insert Into #t 
values ('2010-05-07 13:00:00')

Insert Into #t 
values (Null)

declare @newDate datetime

set @newDate = '2010-07-01'

update #t
Set col1 = DateAdd(day, DateDiff(day, col1, @newDate), Col1)
Where Col1 is not null


select * From #t

drop table #t

I think this should work for you.

create table #t
(
col1 datetime
)

Insert Into #t 
values ('2010-06-01 10:00:00')

Insert Into #t 
values ('2010-06-06 11:00:00')

Insert Into #t 
values ('2010-05-24 12:40:00')

Insert Into #t 
values ('2010-05-07 13:00:00')

Insert Into #t 
values (Null)

declare @newDate datetime

set @newDate = '2010-07-01'

update #t
Set col1 = DateAdd(day, DateDiff(day, col1, @newDate), Col1)
Where Col1 is not null


select * From #t

drop table #t

回复收藏 0 原文