Operations with Data Mysql

Asked

Viewed 984 times

1

I would like to know how to make calculations with dates in Mysql.

Table: 

ID | DataValidade (DATETIME)
1  | 2017-01-01 00:00:00
2  | 2017-05-01 00:00:00
3  | 2016-06-01 00:00:00

I need to return the records whose expiration date is 30 days from today. I need to return records whose expiration date is between periods.

Fez 

SELECT * FROM tabela where DATE_SUB(DataValidade, 30)

but is returning error #1064

Can someone help me or send links where I have good examples I can study regarding mysql date operations?

php mysql

  • 1064 is syntax error, I suspect I’m missing unity, are 30 days, 30 months, 30 years etc?

  • @rray, initially it is 30 days, but operations can vary according to options, type 30 days, months and years

  • vc need to say for Mysql somehow that 30 are days :P

1 answer

2


1064 is the sqlstate for sintexa error, in your case missed the keyword INTERVAL followed by the unit(seconds, minutes, days etc), a functional example would be this:

SELECT date_sub(now(), INTERVAL 1 DAY ) #2016-05-09 12:32:23

The other options of units are in the documentation

This query displays the records 30 days past due:

SELECT * FROM tabela WHERE DataValidade <= DATE_SUB(now(), INTERVAL 30 DAY)

For records that will win trade data_sub() for date_add() adding days(minutes seconds etc) in a date

SELECT * FROM tabela WHERE DataValidade <= DATE_add(now(), INTERVAL 30 DAY)

  • I understood, but in this case how would you select the records that will expire within 30 days? I have the Expiration date recorded in the bank

  • @lelopes vc want the records with date forward or p back?

  • date forward.

  • @lelopes I edited the question, it worked? then the right one is to use date_add()

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.