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!
mvoss2576Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

akshah123Commented:
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

0
mvoss2576Author Commented:
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?
0
snoyes_jwCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

mvoss2576Author Commented:
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?
0
snoyes_jwCommented:
SELECT VERSION();
0
mvoss2576Author Commented:
4.0.2a-nt is the version we appear to be running
0
mvoss2576Author Commented:
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...
0
snoyes_jwCommented:
Then you can use the second version, that is:
`fieldName` = '2005-03-16' + INTERVAL DATE_FORMAT(`fieldName`, '%H:%i:%s') HOUR_SECOND
0
mvoss2576Author Commented:
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?
0
mvoss2576Author Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.