Menu
I store dates in my database in a column of date data type.
Let's say I have column date where I store data like this "2011-01-01", "2012-01-01", "2012-02-02" etc.
Now I need to make SQL that selects only rows where date is equal to 2012
SELECT * FROM table WHERE date=hasSomehowYearEqualTo=2012
What would be the query like?
226
Use SQL Server's YEAR() function if you want to get the year part from a date. This function takes only one argument – a date, in one of the date and time or date data types.
Use YEAR in where clause : Year « Date Time « SQL / MySQL %m returns the month (01-12), %d returns the day (01-31), and %Y returns the year in four digits. Get the payment number and the year of each penalty paid after 1980.
Just run these SQL queries one by one to get the specific element of your current date/time: Current year: SELECT date_part('year', (SELECT current_timestamp)); Current month: SELECT date_part('month', (SELECT current_timestamp)); Current day: SELECT date_part('day', (SELECT current_timestamp));
SQL has IsDate() function which is used to check the passed value is date or not of specified format, it returns 1(true) when the specified value is date otherwise it return 0(false).
Do NOT use YEAR(date) - this will calculate YEAR(date) for all dates, even for those, you never use. It will also make use of an index impossible - worst case on the DB layer.
Use
$sql="SELECT * FROM table WHERE `date` BETWEEN '$year-01-01' AND '$year-12-31'"
As a general rule: If you have the choice between a calculation on a constant and a calculation on a field, use the former.
141
Check out the YEAR() docs function for MySQL
SELECT * FROM table WHERE YEAR(date)=2012
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
