SQL help - dates

Download and get help for different MediaMonkey Addons.

Moderators: Peke, Gurus

Pablo
Posts: 554
Joined: Sun Feb 22, 2004 2:59 am

SQL help - dates

Post by Pablo » Fri Mar 12, 2004 1:46 am

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
Posts: 46097
Joined: Sat Sep 06, 2003 5:53 pm
Location: MediaMonkey 5

Post by Lowlander » Fri Mar 12, 2004 10:06 am

That is possible in most programming languages. The only problem is I don't know the code for VBscript, sorry.
Lowlander (MediaMonkey user since 2003)

Pablo
Posts: 554
Joined: Sun Feb 22, 2004 2:59 am

Post by Pablo » Fri Mar 12, 2004 10:06 pm

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
Posts: 46097
Joined: Sat Sep 06, 2003 5:53 pm
Location: MediaMonkey 5

Post by Lowlander » Fri Mar 12, 2004 10:45 pm

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.
Lowlander (MediaMonkey user since 2003)

roylayer
Posts: 85
Joined: Tue Feb 25, 2003 12:44 am

Post by roylayer » Fri Mar 12, 2004 11:15 pm

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.
Happy user of MediaMonkey Gold version 2.5.5.998
Computer: p4, 2.5 ghz, 3 gb ram, win xp

Post Reply