I am having a huge problem with a select-statement. I am using an access 2002database and I am trying to get a query togeather from an asp-page that selects all records from a table where the date is today. I can't figure out how to solve it.
ex. SELECT * FROM myTable WHERE columnDate = 'todays date'
how do I do it? If anyone could help me I would soooo happy
success will depend on what the datatype of columnDate is, and what sorts of values have been entered into it -- dates where the time component is zero, which you get from the date() function, or dates with a non-zero time component, which you get from the now() function
where columnDate = date()
where columnDate >= date()
and columnDate < dateadd("d",1,date())
for applications where both date and time components are required (e.g. dental appointments), you use two datetime fields, one for the date with no time component, and one for the time with no date component, right? just so the sql is easier?
Correct, so that everything is more compartmentalized (including validating for proper date entry and in a separate field validating for proper time entry), and along with that the SQL is indeed easier.
I would generally never force a user to enter a date AND a time all in the same field.
Don't know that I ever put the difference in hours directly in a SQL statement (as opposed to perhaps calcuating in VBScript after the recordset has already been created), but if this works in a SQL statement:
SELECT DateDiff('h', datetime2, datetime1)
Then I would assume that perhaps this would work too:
SELECT DateDiff('h', date2 + time2, date1 + time1)