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;
05
at the end come from? Your time is is00:00
, so shouldn't the time second part be000000
? Five0
s 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! :-)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?INTERVAL 5 DAY
is not "standard SQL".INTERVAL '5' DAY
would be standard SQL