change day of date and not time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

change day of date and not time

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-16 : 07:59:23

Is there an easy update statement to change the day of a date field but leave the time that is there?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-16 : 08:01:59

update MyTable
set dateCol = dateadd(d, 1, dateCol)
where ...

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 08:55:29

hey, that's quite neat. So to change a date to, say, 16th March,a dn leave the time alone, I could do

update MyTable
set dateCol = dateadd(Day, DATEDIFF(Day, '16 Mar 2005' , dateCol), dateCol)
where ...

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-16 : 09:02:59

well that wouldn't do exactly that but:


declare @table table(id int identity(1,1), dt datetime)
insert into @table
select getdate()-1 union all
select getdate()-2 union all
select getdate()-3 union all
select getdate()-4 

select * from @table

update @table
set dt = dateadd(d, 7, dt)
where id = 2

update @table
set dt = dateadd(Day, DATEDIFF(Day, '16 Mar 2005' , dt), dt)
where id = 3

select * from @table

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 09:34:36

I'm being thick, how is that different?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-16 : 09:36:42

different from what?? i wasn't trying to prove anything...
just gave a code sample.
oh yeah dateadd(Day, DATEDIFF(Day, '16 Mar 2005' , dt), dt) doesn't set the date to 16 march.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 09:50:25

You're right, it needs its sign reversing:

set dt = dateadd(Day, -DATEDIFF(Day, '16 Mar 2005' , dt), dt)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources