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
SQL help - dates
That is possible in most programming languages. The only problem is I don't know the code for VBscript, sorry.
Download MediaMonkey | License
Help: Knowledge Base | MediaMonkey for Windows 5 | MediaMonkey for Android
Lowlander (MediaMonkey user since 2003)
Help: Knowledge Base | MediaMonkey for Windows 5 | MediaMonkey for Android
Lowlander (MediaMonkey user since 2003)
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:
The problem is this doesn't work because '1/1/2004' is not the right format for dates.
Pablo
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'
Pablo
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.
I'm not sure what it should be.
Download MediaMonkey | License
Help: Knowledge Base | MediaMonkey for Windows 5 | MediaMonkey for Android
Lowlander (MediaMonkey user since 2003)
Help: Knowledge Base | MediaMonkey for Windows 5 | MediaMonkey for Android
Lowlander (MediaMonkey user since 2003)
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
Remember, the date is really made up of date AND time, so changing my Where clause to 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.
Code: Select all
SELECT Songs.LastTimePlayed, Songs.*
FROM Songs
WHERE (((Songs.LastTimePlayed)>#3/11/2004#));
Code: Select all
Songs.LastTimePlayed)=#3/12/2004#
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
Computer: p4, 2.5 ghz, 3 gb ram, win xp