Link to home
Start Free TrialLog in
Avatar of mvoss2576
mvoss2576

asked on

Clarification on Updating DATE only in DATETIME field

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!
Avatar of akshah123
akshah123
Flag of United States of America image

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  
MICROSECOND MICROSECONDS  
SECOND SECONDS  
MINUTE MINUTES  
HOUR HOURS  
DAY DAYS  
WEEK WEEKS  
MONTH MONTHS  
QUARTER QUARTERS  
YEAR YEARS  
SECOND_MICROSECOND 'SECONDS.MICROSECONDS'  
MINUTE_MICROSECOND 'MINUTES.MICROSECONDS'  
MINUTE_SECOND 'MINUTES:SECONDS'  
HOUR_MICROSECOND 'HOURS.MICROSECONDS'  
HOUR_SECOND 'HOURS:MINUTES:SECONDS'  
HOUR_MINUTE 'HOURS:MINUTES'  
DAY_MICROSECOND 'DAYS.MICROSECONDS'  
DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS'  
DAY_MINUTE 'DAYS HOURS:MINUTES'  
DAY_HOUR 'DAYS HOURS'  
YEAR_MONTH 'YEARS-MONTHS'  

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

Avatar of mvoss2576
mvoss2576

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of snoyes_jw
snoyes_jw
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
SELECT VERSION();
4.0.2a-nt is the version we appear to be running
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...
Then you can use the second version, that is:
`fieldName` = '2005-03-16' + INTERVAL DATE_FORMAT(`fieldName`, '%H:%i:%s') HOUR_SECOND
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?
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!