mySQL datetime.... Year to date

horndog

I am looking at doing a report and was wondering how would you do an annual report on something using the data type "datetime" in mysql to get Year to Date records?

Same thing for months

DigitalExpl0it

id use the BEWTEEN option in your mysql statement

horndog

how do you use the between arguement in a select statement?

Weedpacket

See your MySQL documentation on the syntax of the SELECT statement. You'd use BETWEEN in the WHERE clause.

horndog

a bit of a newbie here...

so SELECT date from $table Where $today BETWEEN $thisyear?

Weedpacket

Did you read the manual?

hand

http://www.mysql.com/doc/en/Date_and_time_functions.html

horndog

yes, I read the manual, and i still don't see anything regarding a BETWEEN statement nor do i see any examples that would apply to this. I would figure it would be a common query, I guess not!

any help?

nemonoman

From the MySQL manual:
http://www.mysql.com/doc/en/Comparison_Operators.html

BETWEEN syntax

WHERE x
BETWEEN min AND max

so try

BETWEEN ('2002-01-01') AND CURR_DATE

for more on CURR_DATE and other useful functions

see:
http://www.mysql.com/doc/en/Date_and_time_functions.html

Nemo "RTFM" Noman

robsgaming

i finally found a way to do it a while back...

heres an example:

SELECT * FROM Orders WHERE OrderDate BETWEEN '2002:10:01' AND '2002:10:31'

dunno if this helps

horndog

thanks guys! I will post my results when I try to pound out this complex query.

I never had much experience with datetime stuff

horndog

is there a way to extract the current year and use that value instead of hard coding the year into the script?

nemonoman

Have you looked at the link I sent earlier?

http://www.mysql.com/doc/en/Date_an..._functions.html

YEAR(date)
Returns the year for date, in the range 1000 to 9999:

mysql> SELECT YEAR('98-02-03');
-> 1998

YEAR(CURRENT_DATE) would return current year.

Here's a hint before you ask your next question: READ THE MANUAL, then ask.

horndog

ease up... I do read the manual first. I just doesn't make sense