Select record that has a date field using only the year on Where

Asked

Viewed 499 times

1

How can I make a SELECT in the database in which the records have a field that the format is DATE Ex:(2017-05-15) and in the WHERE clause I want for just the year. I also wanted to know if it is better to use the dates in the database in varchar format or date format.

mysql

2 answers

1


You can use the function Year:

SELECT * from tabela WHERE YEAR(campo_data) = '2017'

Or Extract:

SELECT * from tabela WHERE EXTRACT(YEAR FROM campo_data) = '2017'

If you’d better use Date or Varchar will depend on the need, the type of data infers in the resources that will be available. The advantage of using the Date is that specific date functions may be useful, like the one used above.

  • Why would it depend on the need if in my view by what you said the Date provides much more advantages? Does the Date format have a disadvantage compared to Varchar? Or is it a question of the programmer’s taste?

  • 1

    It goes beyond the programmer’s taste, involves semantics, performance and available resources. It works to store a numerical value in a text field, already formatted, but it is not semantic and in mathematical operations it will require conversions. The same fits the date. When it comes to databases, it is important to think about the type of data due indexing and processing time, comparisons, etc.

1

You can also declare in a variable the current year, example: 

$data = date('Y');

Then just play at Where!

Browser other questions tagged mysql

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