Semi-Advanced Date Determination Purely In MySQL

[macharborguy]

Okay, here is the brief information. I have a table that contains a start date in the following format: 'YYYY-MM-DD', as well as an end date in the same format.

I know how to determine the difference between the two dates to get the number of days from start to end, DATEDIFF(2009-01-01,2009-01-02) returns 1, however it doesn't give me the ability to differentiate what that 1 day was, or if there was 2, 3 or 4 days, what each of those days are.

What I want to do is in SQL, determine if the days between 'start' and 'end' falls on a Sunday-Thursday, or are Friday/Saturday, and then have it use those returned values for further usage.

Now for the practical usage. I run a motel (12 rooms, not very big) and am writing management software. We will be changing our rates so that a room is cheaper Sunday night through Thursday night, and $10 more on Friday and Saturday night.

Currently I have a VIEW that contains all of the account ledger calculations for things such as base room rate, number of nights (using DATEDIFF), number of pets, tax calculations, refunds (if any), etc. However, this is based upon a flat base rate, as opposed to dual rates (weekday, weekend).

Is there a way purely in an SQL query for me to not only count the number of night stays (using DATEDIFF), but ALSO split those days into two additional fields, those being 'weekend_night' and 'weekday_night'. So if someone stayed wednesday, thursday, friday and saturday nights, 'nights' would be 4, 'weekday_nights' would be 2 (wed & thurs), and 'weekend_nights' would be 2 (fri & sat).

Once I can populate those two fields I should easily be able to get the ledger VIEW to calculate all of the prices properly.

Thanks ahead of time for any help you can offer.

[rocknbil]

Welcome aboard macharborguy, this may be a little more complicated than it seems. :-) Have you been to the documentation?

mySQL docs [dev.mysql.com]

I don't know what version you run, so here's the date/time functions for 5.0:

Date/Time [dev.mysql.com]

You should be able to do a nested format function within the main statement to give you the day string in a variety of formats. Working out from your example, slightly changed:

select datediff(2009-01-01,2009-01-07);
Note this returns null, use

select datediff('2009-01-01','2009-01-07');

-6

select datediff('2009-01-07','2009-01-01');

6

Now format those dates just to get the day they land on:

select datediff('2009-01-07','2009-01-01') as diff, date_format('2009-01-07', '%W, %M %D, %Y') as end, date_format('2009-01-01', '%W, %M %D, %Y') as start;

should give you

¦diff.........¦end.........................¦start.....................¦ 
¦6............¦Wednesday, January 7th, 2009¦Thursday, January 1st, 2009¦ 

A review of the formats to be extracted should give you what you need; you can extract the day index, the day name/abbreviation, Sunday=day 0 or Monday = day 0 (remembering "0" is the first index,) or anything you want.