Mixing LIKE and BETWEEN

Hi all,

Im using PHP and MySQL to do a date range search..
Can I do a date range search to compare to dates using LIKE and BETWEEN?

e.x.

My Date Format: 01/01/2009 4:00pm

WHERE DateStamp BETWEEN '% 4:00pm' AND '% 5:00pm'";

My syntax is wrong and need a little help...please let me know what I am doing wrong, thank you.

 

You can either use REGEXP:

WHERE datestamp REGEXP '4:[0-9][0-9][ab]m'

Code (markup):

Or date_format

WHERE date_format(datestamp, '%h') = '4'

Code (markup):

Both untested. It's also possible that for the first query you'll have to use date_format first to convert to string.

 

This is beautiful!! Thank You!

I used the first example. now..if I wanted to have it within a range...that is where I am still stuck..

e.x. between 4:00 - 5:00

 

Use
WHERE DATE_FORMAT(date,'%H') >= 16 AND DATE_FORMAT(date,'%H') <= 17

 

Yeah, I was confused by your question as between 4:00 and 5:00 meant to me a range from 4:00 to 4:59, so that's why I only did = '4'. Aquilax' solution should work. If you want to use the regexp solution you can just do

WHERE datestamp REGEXP '[45]:[0-9][0-9][ab]m'

 

You all rock so hard. Rep to both of you! Thank you!

 

1 last question,... i promise...

it is duplicating results for 1:00 and 11:00 any way around this? I am looking up reg expressions but cant seem to find my solution. Thanks!!

 

WHERE datestamp REGEXP '0[15]:[0-9][0-9][ab]m' -> for hours 01 and 05

Or:

WHERE datestamp REGEXP '(01|05):[0-9][0-9][ab]m'

 

WONDERFUL. Wow you are amazing! I am studying to be like you seriously. But I keep getting stuck with unique instances...

LAST REGEXP question.

I have a time frame of 11:**pm, 12:**am, 1:**am, 2:**am i need to pull -
so, essentially the first one is AM and the rest of the times are PM..I am stuck, any ideas?

Thank you!!!!

 

Haha, no problem, just trying to help. Try this regex:

WHERE datestamp REGEXP '(11:[0-9]{2}pm|(12|01|02):[0-9]{2}am)'

Btw, if you want a nice online regex tester, try http://regexpal.com/ -- it will highlight what each line that it matches. Quite nice.

 

This is great. I am learning so much. It's funny actually, how once I see the solution, it just clicks. Like - wow, that totally makes sense now.

Thanks for the help, resources and learning.
BUY FROM PREMIUM SCRIPTS! ++

 

Well, all I can say is it's refreshing to see someone who's actually learning from this stuff instead of just mindlessly copy/pasting the solution