2

This is a basic SQL datetime question, but I don't want to mess my database up!

For example, in a MySQL database, if I want to replace all datetime values between date X and date Y in a single column in one of my tables - what's the SQL command to do it? Example: I want to replace all datetime values in January of this month with a future date/time in March.

I know how to select a range of datetimes thanks to other StackOverflow questions - example:

select * from table where DatetimeField between '22/02/2009 09:00:00.000' and '23/05/2009 10:30:00.000'

But how to add the replace() function to this?

Thanks in advance for your help - I've made a database backup just in case!

Jamison
  • 2,218
  • 4
  • 27
  • 31
  • I don't understand what you are trying to accomplish. Maybe you can show what it does now and what you would like instead? – wallyk Feb 13 '11 at 20:06

3 Answers3

6

You don't need a function, a simple UPDATE statement does the trick. Here is some commented sample SQL code, if I understood your question correctly:

UPDATE Table -- Put in your table name
  SET DateTimeField='31/03/2011 10:30:00.000' -- Change to replacement date/time
  WHERE DatetimeField BETWEEN -- This is the time range to replace, 
    '22/02/2009 09:00:00.000' AND '23/05/2009 10:30:00.000' -- inclusive
Michael Goldshteyn
  • 71,784
  • 24
  • 131
  • 181
1

You use an update query to update values in the table. This will filter out the records with the selected dates and put a new date in the field:

update table
set DatetimeField = '01/03/2009 09:00:00.000'
where DatetimeField between '22/02/2009 09:00:00.000' and '23/05/2009 10:30:00.000'
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
1
UPDATE table
SET DateTimeField = '2011-03-17'
WHERE DateTimeField >= '2011-01-01'
    and DateTimeField < '2011-02-01'

Between is inclusive, so if you want to exclude the exact end date you should use the >= and < syntax.

Jason Goemaat
  • 28,692
  • 15
  • 86
  • 113