Showing results based on a date range

danielhalawi

Hi,

I was wondering if anyone can help.
I have a calendar application, which stores dates as characters in its own column, ( was designed externally and using a now defunct openforge project)
So y = year m = month and d = day.

So the result would be
---m---d-----y---
||2 |||6|||2008||

I'm wanting to return results based on a range.
between 18/5/2008 to 22/6/2008 when I run:

SELECT bs_emp_num, fname, lname, title, m, y, d, bs_code, name, hours, train_travel
FROM `courses_endsession` AS BCE
LEFT JOIN courses_attendance AS BCA ON BCE.atten_bcid = BCA.bcid
INNER JOIN calendar_mssgs AS BCM ON BCA.courseid = BCM.id
INNER JOIN courses AS BC ON BCM.title = BC.cname
INNER JOIN staff AS BST ON BCA.staffid = BST.uid
INNER JOIN store AS BS ON BST.store_num = BS.storenumber
WHERE 
(y = '2008' AND m >= '5' AND d >= '18') 
AND pay_type = 'W'
AND ( y = '2008' AND m = '6' AND d <= '22' ) 
AND pay_type = 'W'"

The above is not getting any results?
What am I doing wrong?

Is there are easier way

leatherback

You are making the misatek of
1) Assuming that if a day falls between the 18th and the 22th that the database understands you want to count through the whole month in between
2) You are not storing dates, but numbers

You could try the mysql str_to_date function and see whether that works.

Personlly, I would look into getting rid of the design and making it all into date colums

danielhalawi

Hi,

I have tried STR_TO_DATE.

STR_TO_DATE('CONCAT(m,'/', d,'/',y)', '%m/%d/%Y') as `daty`

And get NULL as a result?
Could anyone please help?
Is my attempt above wrong?

leatherback

Note: I have never tried anything like this. You might not need thhe quotes around the 'concat()' as it is a function, not a literal value

danielhalawi

Removing the quotes around CONCAT worked.
This now works exactly how I need it.

STR_TO_DATE( CONCAT( m, ' / ', d, ' / ', y ) , '%m/%d/%Y' ) AS `daty`

Thanks for the pointer