Database Design (SQL)
posted by Kwadwo on .
I need help creating a query that shows the number of days left before the end of the term. I need the following functions to accomplish this: CONCAT, DAYOFYEAR, NOW. So far I have this for my query:
SELECT CONCAT('There are ',DAYOFYEAR(NOW()),' ',DAYOFYEAR(NOW())) AS `Days Left`;
The result is supposed to say: There are 69 days left before school ends for the spring term!!!
The result I got was: There are 79 79
There are a few things to note:
1. You need to have a date to specify the end of the term, or else SQL has no way to get the answer you want.
2. The result 79 represents the number of days since January 1st of the year, which correspond to about March 20th.
3. 79 appeared twice because the expression DAYOFYEAR(NOW()) appeared twice.
4. Finally, to find the number of days left, you need to subtract one date from the other, something like:
Assuming 3rd of June is the last day of the term.