?
Solved

Clarification on Updating DATE only in DATETIME field

Posted on 2005-03-11
10
Medium Priority
?
333 Views
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!
0
Comment
Question by:mvoss2576
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 17

Expert Comment

by:akshah123
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  
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
 

Author Comment

by:mvoss2576
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?
0
 
LVL 33

Accepted Solution

by:
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
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:mvoss2576
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?
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 13564758
SELECT VERSION();
0
 

Author Comment

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

Author Comment

by:mvoss2576
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...
0
 
LVL 33

Expert Comment

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

Author Comment

by:mvoss2576
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?
0
 

Author Comment

by:mvoss2576
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!
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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

777 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