I have a field in the table set to DATE and the data is saved in this field as follows: year-month-day hour: minute: second
Exemplo: 2014/04/22 18:32:00
I need to develop a query that returns me only given year / day / month.
A scheme:
IR PARA -> TABELA -> E só pegar o resultados que o ano de 2014 -> 2014-xx-xx xx: xx:xx
Where X is a random value, that is, it returns any value to me because it is the year 2014
In a way that I can do this with the year / month / day ..
For complete dates:
-- Se seu campo for DATE:
SELECT * FROM tabela WHERE DATE(minhadata) = '2014-05-21';
-- Se seu campo for VARCHAR:
SELECT * FROM tabela WHERE LEFT(minhadata,10) = '2014/05/21';
For year only:
-- Se o campo for DATE:
SELECT * FROM tabela WHERE YEAR(minhadata) = '2014';
-- Se o campo for VARCHAR:
SELECT * FROM tabela WHERE LEFT(minhadata,4) = '2014';
For month only:
-- Se o campo for DATE:
SELECT * FROM tabela WHERE MONTH(minhadata) = '05';
-- Se o campo for VARCHAR:
SELECT * FROM tabela WHERE SUBSTR(minhadata, 6, 2) = '05';
For day only: :
-- Se o campo for DATE:
SELECT * FROM tabela WHERE DAY(minhadata) = '23';
-- Se o campo for VARCHAR:
SELECT * FROM tabela WHERE SUBSTR(minhadata, 9, 2) = '23';
Combining the above possibilities
Here's an example of how to get the records for the 17th of every month in 2013:
-- Se o campo for DATE:
SELECT * FROM tabela WHERE
YEAR(minhadata) = '2013' AND
DAY(minhadata) = '17';
-- Se o campo for VARCHAR:
SELECT * FROM tabela WHERE
LEFT(minhadata,4) = '2013' AND
SUBSTR(minhadata, 9, 2) = '17';
Notice that I'm considering the date
VARCHARin the formatAAAA/MM/DD. For other formats, setSUBSTR(campo,posicaoinicial,quantidade)accordingly.
A field of type DATE does not record date in any format, writes an integer internally, the conversion to "date" at the time of the select depends on the date format parameter, to extract the YEAR eg use MySQL FUNCTIONS as YEAR.