MySQL Functions

[driveflexfuel]

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]

just to be sure, database is a table in the database itself?

 

[descalzo]

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...)

[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...)

[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

 

[driveflexfuel]

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.

 

[yellow202]

test

 

[misson]

test

Don't post off-topic to a thread; it's bad netiquette. Start a new thread in the appropriate forum, which is off-topic if there's no other forum.

 

[playminigames]

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.