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 Nov 24 '19 at 14:42
  • Added the images with sample query output. MySql is of most recent version (MySQL Community Server 8.0.18 ). – SQLstudent11242019 Nov 25 '19 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 Nov 25 '19 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. – SQLstudent11242019 Nov 25 '19 at 13:26
  • INTERVAL 5 DAY is not "standard SQL". INTERVAL '5' DAY would be standard SQL – a_horse_with_no_name Nov 25 '19 at 14:30
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? – SQLstudent11242019 Nov 25 '19 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 Nov 25 '19 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. – SQLstudent11242019 Nov 25 '19 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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