Replace - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Replace

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-06 : 13:07:35

I used this:

UPDATE CurrentDiary
SET week_start_date = REPLACE(week_start_date, '7/3/2010 10:54:00 AM', '7/2/2010 10:54:00 AM')

but it replaced all dates in the db (I backed up DB so I was okay).

What I want to do is change 7/3/2010 to 7/2/2010 how do I write that?

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-07 : 00:48:14

The message may says all rows updated because you are updating all the rows without any condition, But it won't update all the row. Check the table for update. Otherwise use the where condition for specific rows update,

UPDATE CurrentDiary
SET week_start_date =REPLACE(week_start_date, convert(datetime,'7/2/2010 10:54:00 AM'), convert(datetime,'7/3/2010 10:54:00 AM'))
where week_start_date=convert(datetime,'7/2/2010 10:54:00 AM')

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-07 : 00:59:25

Hi,

As already explained by Sql-programmers, the message will show all rows updated.

Try this:
Update CurrentDiary
Set week_start_date = '2010-07-02 10:54:00' where week_start_date = '2010-07-03 10:54:00'

Example:

Declare @CurrentDiary table
(week_start_date datetime)

Insert into @CurrentDiary
Select '7/3/2010 10:54:00 AM' union
Select '7/4/2010 10:54:00 AM' union
Select '7/5/2010 10:54:00 AM'

Update @CurrentDiary
Set week_start_date = '2010-07-02 10:54:00' where week_start_date = '2010-07-03 10:54:00'

select * from @CurrentDiary

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-07 : 03:17:49

quote:
Originally posted by pk_bohra

Hi,

As already explained by Sql-programmers, the message will show all rows updated.

Try this:
Update CurrentDiary
Set week_start_date = '2010-07-02 10:54:00' where week_start_date = '2010-07-03 10:54:00'

Example:

Declare @CurrentDiary table
(week_start_date datetime)

Insert into @CurrentDiary
Select '7/3/2010 10:54:00 AM' union
Select '7/4/2010 10:54:00 AM' union
Select '7/5/2010 10:54:00 AM'

Update @CurrentDiary
Set week_start_date = '2010-07-02 10:54:00' where week_start_date = '2010-07-03 10:54:00'

select * from @CurrentDiary

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Always use either YYYYMMDD HH:MM:SS or YYYY-MM-DDTHH:MM:SS format
For more informations refer this
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-07 : 09:23:51

Hi Madhivanam,

I agree that date should always be in YYYYMMDD HH:MM:SS or YYYY-MM-DDTHH:MM:SS format.

The only reason for using the below format is that OP has used this format. So for explanation purpose, i thought it will be better if i use the same format.

Select '7/3/2010 10:54:00 AM' union
Select '7/4/2010 10:54:00 AM' union
Select '7/5/2010 10:54:00 AM'

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources