MySQL Functions

driveflexfuel

New Member
Messages
159
Reaction score
0
Points
0
I have been trying to figure out how to select the following from my database.

Select information between beginning of this week and now()

"SELECT * FROM database WHERE ____ "

Is this correct for selecting all content from the beginning of the month to now

SELECT * FROM database WHERE DATEDIFF(NOW(), date) > 0 AND DATEDIFF(NOW(),DATE_FORMAT(NOW(), '%Y-%m-01')<0) LIMIT 1

Any help is greatly appreciated
 

diabolo

Community Advocate
Community Support
Messages
1,682
Reaction score
32
Points
48
just to be sure, database is a table in the database itself?
 

descalzo

Grim Squeaker
Community Support
Messages
9,373
Reaction score
326
Points
83
Quick thoughts...

Assuming date is a legit date, also not in future....

For this month (assuming you mean calendar month and not 30 days ago...)

Code:
[FONT=Courier New]SELECT * FROM tablename WHERE  MONTH(NOW()) = MONTH( date ) [/FONT]
[FONT=Courier New]           AND YEAR( NOW() ) = YEAR( date )[/FONT]

For this week (assuming you mean calendar week and not 7 days ago...)

Code:
[FONT=Courier New][FONT=Courier New]SELECT * FROM tablename WHERE  WEEK(NOW()) = WEEK( date ) [/FONT]
[FONT=Courier New]           AND YEAR( NOW() ) = YEAR( date )[/FONT]
[/FONT]

as a start. This does fail over New Years, but you could add an OR phrase to cover that.

mySQL WEEK function has a second arguement where you can set whether your week starts on Sunday or Monday.

You might also look into the YEARWEEK function that might fix the New Years problem with WEEK
 
Last edited:

driveflexfuel

New Member
Messages
159
Reaction score
0
Points
0
Thanks again to the all knowing descalzo.

I used your method for month but i did look into YEARWEEK() and decided to go with that.

Thanks again for the help.
 

playminigames

New Member
Messages
216
Reaction score
6
Points
0
you might of had a problem with the LIMIT 1 function, because it limits the result to one, so you wont get all the results.
 
Top