SQL help - dates

[Pablo]

Hi, I need some help with SQL and date fields.
What format should I use to query a date/hour field? (say I want all records where the date is after a given date).
Is there any way to access the current day? (maybe using VBscript)
Can I subtract two dates?

Thanks,

Pablo

[Lowlander]

That is possible in most programming languages. The only problem is I don't know the code for VBscript, sorry.

[Pablo]

Well, I found several functions in VBscript that deal with dates. But I'm still having trouble with SQL.

Let's take a simple example. Suppose that I want to get all songs who were last played between two given dates, say 1/1/2004 and 2/1/2004. My SQL statement should be something like:

Code: Select all

  SELECT * FROM Songs
  WHERE  LastTimePlayed
  BETWEEN '1/1/2004' AND '2/1/2004'

The problem is this doesn't work because '1/1/2004' is not the right format for dates.

Pablo

[Lowlander]

Check how the DB stores and make sure it isn't 01/01/2004 or 01-01-2004.

I'm not sure what it should be.

[roylayer]

Inside Access, dates are delimited by # symbols. Thus, today's date would be #3/12/2004#. A query that returns all songs played today looks like

Code: Select all

SELECT Songs.LastTimePlayed, Songs.*
FROM Songs
WHERE (((Songs.LastTimePlayed)>#3/11/2004#));

Remember, the date is really made up of date AND time, so changing my Where clause to

Code: Select all

Songs.LastTimePlayed)=#3/12/2004#

would not have the same effect. Also, I'm not sure how the different local date formats affect your queries, but that's something to be aware of as well.

Hopefully this will help some.