Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Clarification on Updating DATE only in DATETIME field

Posted on 2005-03-11
Medium Priority
Last Modified: 2008-02-01
I have looked through the knowledgebase, and see what looks like a solution to my problem, but not being a MySQL guru, I need to make sure as the database I am working on is very sensitive ( I did make a backup first!)
I have a field called key_datetime, which appears as 2005-03-11 10:23:30 (or YYYY-MM-DD HH:MM:SS) and I only need to change the YYYY-MM-DD part, and leave the other part alone since the data that came across had the wrong date, but correct time.

The solution I found (but it is for Microsoft SQL) says:
Update[Table Name]
Set [Field Name] = dateadd(dd,datediff(dd,[Field Name],[Required Date]),[Field Name])

I need clarification on what the dateadd is, the dd (is this a value I am supposed to enter?) and the datediff.
Or should I enter
Update [Table Name]
set key_datetime= dateadd(dd,datediff(dd, key_datetime, 2005-03-11),key_datetime);

After the above I would enter my contstraints of what I wanted to change - only certain dates are wrong.

Thanks for help in advance and I hope someone can answer this quickly for me, I have several thousand records to update!
Question by:mvoss2576
  • 6
  • 3
LVL 17

Expert Comment

ID: 13520359
DATE_ADD(date,INTERVAL expr type) , DATE_SUB(date,INTERVAL expr type)

These functions perform date arithmetic. date is a DATETIME or DATE value specifying the starting date. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a '-' for negative intervals. type is a keyword indicating how the expression should be interpreted.

The INTERVAL keyword and the type specifier are not case sensitive.

The following table shows how the type and expr arguments are related:

type Value  Expected expr Format  

The type values DAY_MICROSECOND, HOUR_MICROSECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND, and MICROSECOND are allowed as of MySQL 4.1.1. The values QUARTER and WEEK are allowed as of MySQL 5.0.0.

MySQL allows any punctuation delimiter in the expr format. Those shown in the table are the suggested delimiters. If the date argument is a DATE value and your calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts), the result is a DATE value. Otherwise, the result is a DATETIME value.

As of MySQL 3.23, INTERVAL expr type is allowed on either side of the + operator if the expression on the other side is a date or datetime value. For the - operator, INTERVAL expr type is allowed only on the right side, because it makes no sense to subtract a date or datetime value from an interval. (See examples below.)

mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND;
        -> '1998-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '1997-12-31';
        -> '1998-01-01'
mysql> SELECT '1998-01-01' - INTERVAL 1 SECOND;
        -> '1997-12-31 23:59:59'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
    ->                 INTERVAL 1 SECOND);
        -> '1998-01-01 00:00:00'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
    ->                 INTERVAL 1 DAY);
        -> '1998-01-01 23:59:59'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
    ->                 INTERVAL '1:1' MINUTE_SECOND);
        -> '1998-01-01 00:01:00'
mysql> SELECT DATE_SUB('1998-01-01 00:00:00',
    ->                 INTERVAL '1 1:1:1' DAY_SECOND);
        -> '1997-12-30 22:58:59'
mysql> SELECT DATE_ADD('1998-01-01 00:00:00',
    ->                 INTERVAL '-1 10' DAY_HOUR);
        -> '1997-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    ->            INTERVAL '1.999999' SECOND_MICROSECOND);
        -> '1993-01-01 00:00:01.000001'

If you specify an interval value that is too short (does not include all the interval parts that would be expected from the type keyword), MySQL assumes that you have left out the leftmost parts of the interval value. For example, if you specify a type of DAY_SECOND, the value of expr is expected to have days, hours, minutes, and seconds parts. If you specify a value like '1:10', MySQL assumes that the days and hours parts are missing and the value represents minutes and seconds. In other words, '1:10' DAY_SECOND is interpreted in such a way that it is equivalent to '1:10' MINUTE_SECOND. This is analogous to the way that MySQL interprets TIME values as representing elapsed time rather than as time of day.

If you add to or subtract from a date value something that contains a time part, the result is automatically converted to a datetime value:

mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 DAY);
        -> '1999-01-02'
mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
        -> '1999-01-01 01:00:00'

If you use really malformed dates, the result is NULL. If you add MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month:

mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
        -> '1998-02-28'

-- taken out from http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html


Author Comment

ID: 13535756
Thanks akshah123  BUT:
I have read all that... and I think I am missing something in the interpretation - like I said I am not a MySQL Guru - I know just enough to be dangerous.  The person who used to manage a lot of the databases no longer works here, and I was left to quickly learn - and am still learning. I know simple commands (Select, Update, Delete....) but when you start getting into the DATE_ADD and DateDiff functions, I get lost easily. I need someone to explain or interpret in a different way what the functions actually do and the syntax to use them.

Many Thanks - I already updated the records, but I had to do it the hard way :(
I would still like to know an easy way to do it, so the question is still fair game - how do I update the DATE only in a datetime field, leaving the time intact?
LVL 33

Accepted Solution

snoyes_jw earned 2000 total points
ID: 13559398
dateadd(dd,datediff(dd,[Field Name],[Required Date]),[Field Name])

dateadd - a function to add dates
dd - indicates that we're adding some days, as opposed to seconds or years
datediff - a function to find the time elapsed between dates
dd - indicates we want the number of days between the dates, as opposed to seconds or years
[Field Name] - the SQL interpeter will look up the date stored in this field
[Required Date] - you'd put this in yourself

So what this does:
- Grab the old date from the db [Field Name]
- Grab the date that it is supposed to be [Required Date]
- Find the number of days between the two
- Add this number of days to the old date in the db

The MySQL syntax for this depends on the version of MySQL - there were several useful functions added in version 4.1.1.  Here's how you'd change the dates to 2005-03-16:
`fieldName` = `fieldName` + INTERVAL DATE_DIFF('2005-03-16', `fieldName`) DAY

This appers to be kind of a roundabout way, and it uses DATE_DIFF, which was added in 4.1.1.  You could instead do:
`fieldName` = '2005-03-16' + INTERVAL DATE_FORMAT(`fieldName`, '%H:%i:%s') HOUR_SECOND

However, if all the dates in the DB are uniformly off, e.g. they are all exactly one day behind, you could do
`fieldName` = `fieldName` + INTERVAL 1 DAY
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


Author Comment

ID: 13564511
Let me try that - just so happens I have a few records in a saved database that are incorrect.
How do I find out what version of MySQL he used?
LVL 33

Expert Comment

ID: 13564758

Author Comment

ID: 13592295
4.0.2a-nt is the version we appear to be running

Author Comment

ID: 13592403
Oh... not even 4.0... that was what MY computer here was running (about to update that) the databases in the field are 3.23...
LVL 33

Expert Comment

ID: 13592577
Then you can use the second version, that is:
`fieldName` = '2005-03-16' + INTERVAL DATE_FORMAT(`fieldName`, '%H:%i:%s') HOUR_SECOND

Author Comment

ID: 13593072
I am getting an error when I try this.
calls = '2005-03-20' + INTERVAL DATE_FORMAT(calls, %H:%i:%s) HOUR_SECOND;

Am I missing the right syntax for the quotations?

Author Comment

ID: 13593779
Nevermind... I had a brain fart on a Monday... I was using the database name instead of the Field Name....
Here is what I used finally:
update calls set  key_datetime= "2005-03-20" + INTERVAL DATE_FORMAT(key_datetime, "%H:%i:%s") HOUR_SECOND;

Thanks for all your help snoyes - you made my life much easier - I will post another question here for more points about changing another datetime field in the same database, using two different fields.

Thanks again!

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question