Using server time to pull asp records

..a new record pulled for each day

[midlman]

I'm making a site with frontpage using the database interface wizard. I need to know how to display a certain database record based on the current date. I'v looked all over FP with no luck.

Lets say I have 7 db entries, each with their own key of the day of the week. How do I use the server time as a variable to compare and pull the right record?

Thanks -

[txbakers]

I really depends on the database you are using.

In mySQL, there is a built-in variable called "CURRENT_DATE" which is today.

"select field from table where datefield ='" + CURRENT_DATE + "'"

in Access it's just "DATE()"

"select field from table where datefield =#" + DATE() + "#"

[midlman]

Thanks TXbakers

I believe its an Access database. I think this is the line of code, just the syntax is a little different than what was posted.

fp_sQry="SELECT * FROM Results WHERE (startdate = '::today::')"

>>"select field from table where datefield =#" + DATE() + "#"

what is the format of DATE() so i can be sure it will match datefield?

[duckhunter]

You can force the format in VBScript.

FormatDateTime(now,vbShortDate) will return 5/9/2004
Note: The format of vbShortDate is dependent on the Regional Settings on the server set in Control Panel.

Another idea for you so you don't have to have a record for every day in the future. Have your Database "date" column be a DayOfWeek column instead (1-7). Then calculate the Day of Week in script and you will only require 7 records at any given time.

[midlman]

I added Date() but the record is only returned if the datefield matches the word "Date()" exactly. Plus, I dont know the format of Date() so I dont know what to match.
It seems like this should be a lot easier. On a particular day, I need the records with that same date in the datefield to display.
Any more help?
Thanks!