0

Assume that I have a table Purchases with column named PurchaseDate and with data format of 'YYYY-MM-DD HH:MI:SS'.
Suppose that I want to see the entire column of dates with the days increasing by 5.

Suppose I form the query in the following way:

SELECT PurchaseDate+5 FROM Purchases; 

The result will be given in a form of single numbers, and the increased part will be seconds instead of days.
I.e.: This 1982-09-07 00:00 will turn into this 198209070005.

How do I form the right standard SQL query?

UPDATE: I've got answer. Thanks.

-- this will work for standard SQL:

SELECT PurchaseDate, PurchaseDate + INTERVAL 5 DAY FROM Purchases;

-- this will work for MySQL:

SELECT PurchaseDate, ADDDATE(PurchaseDate, INTERVAL 10 DAY) FROM Purchases; 
6
  • Whereby does the 05 at the end come from? Your time is is 00:00, so shouldn't the time second part be 000000? Five 0s because there are 86400 seconds in a day! Please provide the output of show create table your_table\G; indicating the relevant field. A couple of sample inserts would be good also! Please also provide your version of MySQL. p.s. welcome to the forum! :-)
    – Vérace
    Commented Nov 24, 2019 at 14:42
  • Added the images with sample query output. MySql is of most recent version (MySQL Community Server 8.0.18 ). Commented Nov 25, 2019 at 7:44
  • Screenshots are discouraged here for reasons outlined in the link. Please post what I asked for - the output of SHOW CREATE TABLE my_tab\G as text. We need to know the datatypes of your fields. Are you transforming a real date into a string (e.g. 1948-12-08 00:00:00'19481208000005')? I'm having difficulty in understanding your request - what is the reason for this particular transformation? What is the new datum's datatype?How do you subsequently use that transformed data?
    – Vérace
    Commented Nov 25, 2019 at 8:41
  • Are you transforming a real date into a string - No, this was undesired behavior. And i don't know the reason behind removal of DATE data type; This is a part of my question. But i've got desired results after double check. The Answer below is correct and suitable. Commented Nov 25, 2019 at 13:26
  • INTERVAL 5 DAY is not "standard SQL". INTERVAL '5' DAY would be standard SQL
    – user1822
    Commented Nov 25, 2019 at 14:30

1 Answer 1

2

Asuming the column has a datetime type, simply:

SELECT PurchaseDate + INTERVAL 5 DAY FROM

If it was a string type, you would have to parse it first.

3
  • Is this expression valid for MySql only? AFAIK, standard SQL does not recognize INTERVAL. Does standard SQL have means to do the same? Commented Nov 25, 2019 at 7:23
  • interval is a standard sql92 type- which RDBMS implement it and how, I don't know. You can always use also a function like ADDDATE(PurchaseDate, 5), but that is definitely MySQL-only.
    – jynus
    Commented Nov 25, 2019 at 8:27
  • You right, Your first answer is a standard SQL query. Marked as answer. SELECT OrderDate, ADDDATE(OrderDate, INTERVAL 10 DAY) FROM Orders; Yes. This will work for MySQL. Commented Nov 25, 2019 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.