Hi,

We have a form where the user selects a date from a calendar, the date
is in the format May 23, 2008.

The date in the datebase is part of a filename (varchar column) and is
in the format 05212008.

What we need to do is get the starting and ending date of the week for
the date selected in the form: May 23, 2008, and then get the records
from the database where the dates fall within that range.

Parsing the filename of the date: ABCD.05212008 is not a problem, how
to convert the formats, find the starting and ending dates is.......

I'm still searching.......


Thanks!


John

>We have a form where the user selects a date from a calendar, the date
>is in the format May 23, 2008.

Mysql doesn't do selecting from a calendar, so presumably this is done
from something else, say, using HTML. There is no necessary reason why
it has to return the month as "May" just because that is the way it
is presented to the user.

If you have that date in the format above as datestr, then
str_to_date(datestr, '%M %d, %Y')
will give you a MySQL date for that date.


>The date in the datebase is part of a filename (varchar column) and is
>in the format 05212008.

If you get the date into a MySQL date form, then the filename you
want is date_format(thedate, '%m%d%Y').

>What we need to do is get the starting and ending date of the week for
>the date selected in the form: May 23, 2008, and then get the records
>from the database where the dates fall within that range.

How do you define a week? Does it start on Sunday, Monday, or
something else? You may think the answer to that question is
obvious, but in other countries that same answer is obviously
strange. (MySQL likes to use its date type for dates, so don't
fight it and use them at least as an intermediate form for calculations,
and preferably also for storage. You can use date_format and
str_to_date to change formats for communicating with users.)

If you have a date thedate, then:

subdate(thedate, (weekday(thedate)+1) % 7)

is the first Sunday on or before thedate. To get the Saturday after
that, add 6 days with adddate() to the beginning number above.

The idea here: figure out how many days you are into the week, and
subtract that to get to the beginning of the week. weekday() returns
0 for Monday. If you want weeks that begin with Monday, leave out
the +1 above in the calculation of the first day of the week.

>Parsing the filename of the date: ABCD.05212008 is not a problem, how
>to convert the formats, find the starting and ending dates is.......

Mtek wrote:
> Hi,
>
> We have a form where the user selects a date from a calendar, the date
> is in the format May 23, 2008.
>
> The date in the datebase is part of a filename (varchar column) and is
> in the format 05212008.
>
> What we need to do is get the starting and ending date of the week for
> the date selected in the form: May 23, 2008, and then get the records
> from the database where the dates fall within that range.
>
> Parsing the filename of the date: ABCD.05212008 is not a problem, how
> to convert the formats, find the starting and ending dates is.......
>
> I'm still searching.......
>
>
> Thanks!
>
>
> John

Loads of things wrong here!
First of all mmddyyyy is a crazy way to name files. If you named the file as
yyyymmddABCD.fff
Then when listed they could easily be sorted in date order.
Next, if you want to do date calculations then you should hold the date in a
field of its own with data type DATE. You can have it as well as the
filenname if you want.
Now once you have the date in its own column, you can use the WEEK() or
WEEKOFYEAR() functions to simplify your query.