Dates and formats

[banksie]

Hi Guys, another newbie here.
I have an MS Access database providing data to my web site regarding holidays. Each holiday is stored in one table and includes a departureDate in a standard dd-mm-yy format. Here's the problem that I'm experiencing:
1. I'm trying to offer, on the web page, a drop-down menu that picks up each month in which we have holidays (i.e. we may have 20 holidays leaving in September 2004 but I only want one instance of "September 2004" to appear in the drop-down).
2. Upon selection of the month/year from the drop-down I then want to pass the vistor on to a summarised list of all of the holidays departing in that month which will then feed into
3. A third page which contains all of the relevany information about that selected holiday.
My problem has been in converting the values of the date field in the table into a string that reads as required in a 'mmmm'&' '&'yyyy' format and then converting back the mmmm and yyyy so that I can requery the database for all holidays that contain the mm-yy values.
Anyone got any ideas or can point me to any tutorials etc.that may help.
I started off thinking this would be easy and now wish I'd stayed in bed!

[Smokie]

Is it your SQL that you are having a problem with?

Could you post the ASP code with doesnt work?

[Webforumz Staff]

<blockquote id="quote"><font size="1" face="geneva, verdana, arial" id="quote">quote:<hr height="1" noshade id="quote">1. I'm trying to offer, on the web page, a drop-down menu that picks up each month in which we have holidays (i.e. we may have 20 holidays leaving in September 2004 but I only want one instance of "September 2004" to appear in the drop-down).<hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote">
You want to use DISTINCT in your SELECT statement to get something to occur only once.
<blockquote id="quote"><font size="1" face="geneva, verdana, arial" id="quote">quote:<hr height="1" noshade id="quote">My problem has been in converting the values of the date field <hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote">
I would say keep the date in the database format the entire time, and just reformat it for display when necessary so you don't have to convert it back and forth.

use FormatDateTime(Date[, NamedFormat]) for displaying the date in different ways: http://msdn.microsoft.com/library/en...atDateTime.asp

to convert a string to a date use Date(string)

[banksie]

Thanks Catalyst.
I tried DISTINCT first off but that still listed days - OK if I had 3 holidays starting on one particular day but gives me problems when I tried to apply it to only the mm/yy part of the date field. If I have, say, 20 holidays throughout August, it gave me 20 unique days in August as opposed to the one value required - "August 2004".

Looking at the MSDN page you referred me to, I can't see how, if I'm just to change the format for display purposes in my drop-down, I can just extract and display the month & year to give me "August 2004", "September 2004" etc.

I've cobbled together (with some help) the following that gives me the values and labels for my drop down, but can't then get the selected value into a format that my second page can find all of the holidays in that selected month....

SELECT Format([departureDate],'mm' & '/' & 'yy') AS valueMonth, Format([departureDate],'mmmm' & ' ' & 'yyyy') AS labelMonth
FROM TBLHolidays GROUP BY Format([departureDate],'mm' & '/' & 'yy'), Format([departureDate],'mmmm' & ' ' & 'yyyy'), Format([departureDate],'yyyy'), Format([departureDate],'mm')
ORDER BY Format([departureDate],'yyyy'), Format([departureDate],'mm');

Any suggestions??

Regards
Banksie

[D3mon]

My tip for this task and (others involving dates and databases) is:
Always work with the long version of the date when entering dates into the database to prevent problems associated with internationalisation.

e.g. When writing a date to a time/date type field use '4 may 2004' rather than '04/05/04'. This will eliminate any 'Was that 4 may (UK) or 5 April (US)?' confusion later. Similarly, get the database to return the long version to prevent confusion in your code.

[Webforumz Staff]

<blockquote id="quote"><font size="1" face="geneva, verdana, arial" id="quote">quote:<hr height="1" noshade id="quote">Looking at the MSDN page you referred me to, I can't see how, if I'm just to change the format for display purposes in my drop-down, I can just extract and display the month & year to give me "August 2004", "September 2004" etc.
<hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote">
For that you'd use Month(date) and Year(date) which extract the month and year, or MonthName(Month(date)) to get the string version of the month name instead of the number of the month.
<blockquote id="quote"><font size="1" face="geneva, verdana, arial" id="quote">quote:<hr height="1" noshade id="quote">but can't then get the selected value into a format that my second page can find all of the holidays in that selected month....<hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote">
Use VALUE in your dropdown box OPTION tags to set a numeric date that corresponds with the shown date, like:

Code: Select all

<OPTION value="July 1, 2003">July 2003</OPTION>
<OPTION value="August 1, 2003">August 2003</OPTION>

etc

Then on the next page do your WHERE clause with

Code: Select all

...WHERE eventdate >= #" & passedvalue & "# AND eventdate < #" & DateAdd("m",1,passedvalue) & "#"

DateAdd will take care of problems like how many days in a month, or what happens in December etc.

[GillBates]

<blockquote id="quote"><font size="1" face="geneva, verdana, arial" id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Catalyst
Then on the next page do your WHERE clause with

Code: Select all

...WHERE eventdate >= #" & passedvalue & "# AND eventdate < #" & DateAdd("m",1,passedvalue) & "#"

DateAdd will take care of problems like how many days in a month, or what happens in December etc.
<hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote">
Can also use DAY / MONTH / YEAR etc within your SQL statement:

"SELECT * FROM mytable WHERE Month(mydatefield) = " & passedvalue

Of course, if you had a dropdown of months, you need the values from 1-12 to be passed. Can do similar with YEAR and DAY to find records relating to specific periods.