I've searched the list archives and just don't see what I'm looking for. I'm trying to do a
Select on records against a Date/Time field where the date is sometime within the last 30 days.
I don't care about the time of day. So I have:

WHERE DateTimeField > some-variable

Exactly how do I set this up? Thanks in advance for your reply.


~8^) Pat Wong (ICQ #61070813)
http://www.napathon.com/
-------------------------------------------------------------------
Senility Prayer:
God grant me the senility to forget the people I never
liked anyway, the good fortune to run into the ones I
like, and the eyesight to tell the difference.
-------------------------------------------------------------------

if you are using a variable, use cDate(some-variable).  ASP will make all 
variables text no matter where it comes from, so convert it first, then 
try the comparison and see how that works.  even if your variable hold 
3/2/00 or something like that, it needs to be converted.

in case you didnt know: cDate is a function that converts values to dates.

if it doesnt work, send the code and ill try to re-produce the problem.

let me know

ron

**IT'S HARD TO HELP WITHOUT CODE GUYS/GIRLS!!!!**

> I've searched the list archives and just don't see what I'm looking for. 
I'm trying to do a
Select on records against a Date/Time field where the date is sometime 
within the last 30 days.
I don't care about the time of day. So I have:

WHERE DateTimeField > some-variable

Exactly how do I set this up? Thanks in advance for your reply.


~8^) Pat Wong (ICQ #61070813)
http://www.napathon.com/
-------------------------------------------------------------------
Senility Prayer:
God grant me the senility to forget the people I never
liked anyway, the good fortune to run into the ones I
like, and the eyesight to tell the difference.
-------------------------------------------------------------------

What database are you using? - I'm sure it'll have some inbuilt function,
but how that function works is different from database to database.

-Kim

-----Original Message-----
From: Pat Wong [mailto:vinyl-junkie@n...]
Sent: 10. januar 2003 04:33
To: ASP Databases
Subject: [asp_databases] Date Time Select Problem


I've searched the list archives and just don't see what I'm looking for. I'm
trying to do a
Select on records against a Date/Time field where the date is sometime
within the last 30 days.
I don't care about the time of day. So I have:

WHERE DateTimeField > some-variable

Exactly how do I set this up? Thanks in advance for your reply.


~8^) Pat Wong (ICQ #61070813)
http://www.napathon.com/
-------------------------------------------------------------------
Senility Prayer:
God grant me the senility to forget the people I never
liked anyway, the good fortune to run into the ones I
like, and the eyesight to tell the difference.
-------------------------------------------------------------------

I guess maybe I wasn't too clear about what I'm trying to do. I'm trying to filter records
using the Select statement. I know that I can probably select all the records, parse out the
date from the DateAdded (date/time) field and compare the date in the database to the date
range I'm looking for to see whether to accept or reject the record.

I should also point out that modifying the database to have date and time as separate fields
isn't an option. The database I'm using is an Access backend to some purchased software.

I'd like to filter them using the Select statement instead. Here's what I came up with, but it
doesn't work.

SelectDate = Date - 31

SELECT tblAlbums.Title AS title,
tblAlbums.MediaType AS format,
tblAlbums.AlbumID AS albumid,
tblAlbums.Genre AS genre,
tblAlbums.UserField1 as userfield1,
tblAlbums.DateAdded,
tblArtists.The AS artist_the,
tblArtists.Artist AS artist,
tblArtists.ArtistID as artistid,
tblArtists.SortName AS sortname
FROM tblArtists INNER JOIN tblAlbums ON tblArtists.ArtistID = tblAlbums.ArtistID
WHERE DateAdded > SelectDate
ORDER BY sortname,title;

DateAdded is the date/time field I was talking about. I stuck a Response.Write in the code to
see what value was in SelectDate (which was 12/9/2002 12:00:00 AM).  So the question is - Is
there a way to filter records using the Select statement on a date/time field? If so, how?

----- Original Message -----
From: "Ron Williams" <ronwilliams32@c...>
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, January 10, 2003 3:53 PM
Subject: [asp_databases] Re: Date Time Select Problem


| if you are using a variable, use cDate(some-variable).  ASP will make all
| variables text no matter where it comes from, so convert it first, then
| try the comparison and see how that works.  even if your variable hold
| 3/2/00 or something like that, it needs to be converted.
|
| in case you didnt know: cDate is a function that converts values to dates.
|
| if it doesnt work, send the code and ill try to re-produce the problem.
|
| let me know
|
| ron
|
| **IT'S HARD TO HELP WITHOUT CODE GUYS/GIRLS!!!!**
|
| > I've searched the list archives and just don't see what I'm looking for.
| I'm trying to do a
| Select on records against a Date/Time field where the date is sometime
| within the last 30 days.
| I don't care about the time of day. So I have:
|
| WHERE DateTimeField > some-variable
|
| Exactly how do I set this up? Thanks in advance for your reply.
|
|
| ~8^) Pat Wong (ICQ #61070813)
| http://www.napathon.com/
| -------------------------------------------------------------------
| Senility Prayer:
| God grant me the senility to forget the people I never
| liked anyway, the good fortune to run into the ones I
| like, and the eyesight to tell the difference.
| -------------------------------------------------------------------
|
|

The database I'm using is called CD Trustee. As I said in my original post, it uses MS Access
as a back end to the software.  If you're interested you can read about CD Trustee here:
http://www.base40.com/

You're correct that it has several built-in functions. However, the only built-in function that
has anything to do with a date at all is listing albums by year. That's not the field I need
for my query. In fact, there is nothing I can see that's already built into the database that
would get me where I want to go.  :o(

So I am still left with my original question - I want to Select all albums that were put into
the database within the last 30 days. How do I set up my Select statement to filter based on a
date/time field? Is it even possible to do that?

----- Original Message -----
From: "Kim Iwan Hansen" <kimiwan@k...>
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, January 10, 2003 8:17 AM
Subject: [asp_databases] RE: Date Time Select Problem


| What database are you using? - I'm sure it'll have some inbuilt function,
| but how that function works is different from database to database.
|
| -Kim
|
| -----Original Message-----
| From: Pat Wong [mailto:vinyl-junkie@n...]
| Sent: 10. januar 2003 04:33
| To: ASP Databases
| Subject: [asp_databases] Date Time Select Problem
|
|
| I've searched the list archives and just don't see what I'm looking for. I'm
| trying to do a
| Select on records against a Date/Time field where the date is sometime
| within the last 30 days.
| I don't care about the time of day. So I have:
|
| WHERE DateTimeField > some-variable
|
| Exactly how do I set this up? Thanks in advance for your reply.
|
|
| ~8^) Pat Wong (ICQ #61070813)
| http://www.napathon.com/
| -------------------------------------------------------------------
| Senility Prayer:
| God grant me the senility to forget the people I never
| liked anyway, the good fortune to run into the ones I
| like, and the eyesight to tell the difference.
| -------------------------------------------------------------------
|
|
|
|
|
|

The answer is yes you can.  How?  exactly how you are doing it.  the built-
in function should come on the ASP side, not the database side, 
assuming "selectdate" is a variable on your ASP page.  just make sure 
selectdate is a date, and you do that by converting it to a date, for 
example where dateadded > cDate(selectdate).

i am also assuming you are using ADO, ASP, and VBScript.  if not, than 
sorry, i cant help much.

ron


> I guess maybe I wasn't too clear about what I'm trying to do. I'm trying 
to filter records
using the Select statement. I know that I can probably select all the 
records, parse out the
date from the DateAdded (date/time) field and compare the date in the 
database to the date
range I'm looking for to see whether to accept or reject the record.

I should also point out that modifying the database to have date and time 
as separate fields
isn't an option. The database I'm using is an Access backend to some 
purchased software.

I'd like to filter them using the Select statement instead. Here's what I 
came up with, but it
doesn't work.

SelectDate = Date - 31

SELECT tblAlbums.Title AS title,
tblAlbums.MediaType AS format,
tblAlbums.AlbumID AS albumid,
tblAlbums.Genre AS genre,
tblAlbums.UserField1 as userfield1,
tblAlbums.DateAdded,
tblArtists.The AS artist_the,
tblArtists.Artist AS artist,
tblArtists.ArtistID as artistid,
tblArtists.SortName AS sortname
FROM tblArtists INNER JOIN tblAlbums ON tblArtists.ArtistID = 
tblAlbums.ArtistID
WHERE DateAdded > SelectDate
ORDER BY sortname,title;

DateAdded is the date/time field I was talking about. I stuck a 
Response.Write in the code to
see what value was in SelectDate (which was 12/9/2002 12:00:00 AM).  So 
the question is - Is
there a way to filter records using the Select statement on a date/time 
field? If so, how?

----- Original Message -----
From: "Ron Williams" <ronwilliams32@c...>
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, January 10, 2003 3:53 PM
Subject: [asp_databases] Re: Date Time Select Problem


| if you are using a variable, use cDate(some-variable).  ASP will make all
| variables text no matter where it comes from, so convert it first, then
| try the comparison and see how that works.  even if your variable hold
| 3/2/00 or something like that, it needs to be converted.
|
| in case you didnt know: cDate is a function that converts values to 
dates.
|
| if it doesnt work, send the code and ill try to re-produce the problem.
|
| let me know
|
| ron
|
| **IT'S HARD TO HELP WITHOUT CODE GUYS/GIRLS!!!!**
|
| > I've searched the list archives and just don't see what I'm looking 
for.
| I'm trying to do a
| Select on records against a Date/Time field where the date is sometime
| within the last 30 days.
| I don't care about the time of day. So I have:
|
| WHERE DateTimeField > some-variable
|
| Exactly how do I set this up? Thanks in advance for your reply.
|
|
| ~8^) Pat Wong (ICQ #61070813)
| http://www.napathon.com/
| -------------------------------------------------------------------
| Senility Prayer:
| God grant me the senility to forget the people I never
| liked anyway, the good fortune to run into the ones I
| like, and the eyesight to tell the difference.
| -------------------------------------------------------------------
|
|

Sorry to be so dense, but I just don't understand why my code won't work. I've looked at the
DateAdded field in MS Access, and it definitely is being populated. Yet when my ASP page loads,
it's selecting every single record from the database. Furthermore, when I add the following
statement

Response.Write "Date Added = " & DateAdded

it displays like this:

Date Added 

It's like it isn't there! What am I missing?

----- Original Message -----
From: "Ron Williams" <ronwilliams32@c...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, January 11, 2003 7:33 PM
Subject: [asp_databases] Re: Date Time Select Problem


| The answer is yes you can.  How?  exactly how you are doing it.  the built-
| in function should come on the ASP side, not the database side,
| assuming "selectdate" is a variable on your ASP page.  just make sure
| selectdate is a date, and you do that by converting it to a date, for
| example where dateadded > cDate(selectdate).
|
| i am also assuming you are using ADO, ASP, and VBScript.  if not, than
| sorry, i cant help much.
|
| ron
|
|
| > I guess maybe I wasn't too clear about what I'm trying to do. I'm trying
| to filter records
| using the Select statement. I know that I can probably select all the
| records, parse out the
| date from the DateAdded (date/time) field and compare the date in the
| database to the date
| range I'm looking for to see whether to accept or reject the record.
|
| I should also point out that modifying the database to have date and time
| as separate fields
| isn't an option. The database I'm using is an Access backend to some
| purchased software.
|
| I'd like to filter them using the Select statement instead. Here's what I
| came up with, but it
| doesn't work.
|
| SelectDate = Date - 31
|
| SELECT tblAlbums.Title AS title,
| tblAlbums.MediaType AS format,
| tblAlbums.AlbumID AS albumid,
| tblAlbums.Genre AS genre,
| tblAlbums.UserField1 as userfield1,
| tblAlbums.DateAdded,
| tblArtists.The AS artist_the,
| tblArtists.Artist AS artist,
| tblArtists.ArtistID as artistid,
| tblArtists.SortName AS sortname
| FROM tblArtists INNER JOIN tblAlbums ON tblArtists.ArtistID 
| tblAlbums.ArtistID
| WHERE DateAdded > SelectDate
| ORDER BY sortname,title;
|
| DateAdded is the date/time field I was talking about. I stuck a
| Response.Write in the code to
| see what value was in SelectDate (which was 12/9/2002 12:00:00 AM).  So
| the question is - Is
| there a way to filter records using the Select statement on a date/time
| field? If so, how?
|
| ----- Original Message -----
| From: "Ron Williams" <ronwilliams32@c...>
| To: "ASP Databases" <asp_databases@p...>
| Sent: Friday, January 10, 2003 3:53 PM
| Subject: [asp_databases] Re: Date Time Select Problem
|
|
| | if you are using a variable, use cDate(some-variable).  ASP will make all
| | variables text no matter where it comes from, so convert it first, then
| | try the comparison and see how that works.  even if your variable hold
| | 3/2/00 or something like that, it needs to be converted.
| |
| | in case you didnt know: cDate is a function that converts values to
| dates.
| |
| | if it doesnt work, send the code and ill try to re-produce the problem.
| |
| | let me know
| |
| | ron
| |
| | **IT'S HARD TO HELP WITHOUT CODE GUYS/GIRLS!!!!**
| |
| | > I've searched the list archives and just don't see what I'm looking
| for.
| | I'm trying to do a
| | Select on records against a Date/Time field where the date is sometime
| | within the last 30 days.
| | I don't care about the time of day. So I have:
| |
| | WHERE DateTimeField > some-variable
| |
| | Exactly how do I set this up? Thanks in advance for your reply.
| |
| |
| | ~8^) Pat Wong (ICQ #61070813)
| | http://www.napathon.com/
| | -------------------------------------------------------------------
| | Senility Prayer:
| | God grant me the senility to forget the people I never
| | liked anyway, the good fortune to run into the ones I
| | like, and the eyesight to tell the difference.
| | -------------------------------------------------------------------
| |
| |
|
|

Try this: it may or may not work:


Response.Write "Date Added = " & "#" & DateAdded & "#"

Later,
bware

-----Original Message-----
From: Pat Wong [mailto:vinyl-junkie@n...]
Sent: Saturday, January 11, 2003 2:57 PM
To: ASP Databases
Subject: [asp_databases] Re: Date Time Select Problem

Sorry to be so dense, but I just don't understand why my code won't work.
I've looked at the
DateAdded field in MS Access, and it definitely is being populated. Yet when
my ASP page loads,
it's selecting every single record from the database. Furthermore, when I
add the following
statement

Response.Write "Date Added = " & DateAdded

it displays like this:

Date Added 

It's like it isn't there! What am I missing?

----- Original Message -----
From: "Ron Williams" <ronwilliams32@c...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, January 11, 2003 7:33 PM
Subject: [asp_databases] Re: Date Time Select Problem


| The answer is yes you can.  How?  exactly how you are doing it.  the
built-
| in function should come on the ASP side, not the database side,
| assuming "selectdate" is a variable on your ASP page.  just make sure
| selectdate is a date, and you do that by converting it to a date, for
| example where dateadded > cDate(selectdate).
|
| i am also assuming you are using ADO, ASP, and VBScript.  if not, than
| sorry, i cant help much.
|
| ron
|
|
| > I guess maybe I wasn't too clear about what I'm trying to do. I'm trying
| to filter records
| using the Select statement. I know that I can probably select all the
| records, parse out the
| date from the DateAdded (date/time) field and compare the date in the
| database to the date
| range I'm looking for to see whether to accept or reject the record.
|
| I should also point out that modifying the database to have date and time
| as separate fields
| isn't an option. The database I'm using is an Access backend to some
| purchased software.
|
| I'd like to filter them using the Select statement instead. Here's what I
| came up with, but it
| doesn't work.
|
| SelectDate = Date - 31
|
| SELECT tblAlbums.Title AS title,
| tblAlbums.MediaType AS format,
| tblAlbums.AlbumID AS albumid,
| tblAlbums.Genre AS genre,
| tblAlbums.UserField1 as userfield1,
| tblAlbums.DateAdded,
| tblArtists.The AS artist_the,
| tblArtists.Artist AS artist,
| tblArtists.ArtistID as artistid,
| tblArtists.SortName AS sortname
| FROM tblArtists INNER JOIN tblAlbums ON tblArtists.ArtistID 
| tblAlbums.ArtistID
| WHERE DateAdded > SelectDate
| ORDER BY sortname,title;
|
| DateAdded is the date/time field I was talking about. I stuck a
| Response.Write in the code to
| see what value was in SelectDate (which was 12/9/2002 12:00:00 AM).  So
| the question is - Is
| there a way to filter records using the Select statement on a date/time
| field? If so, how?
|
| ----- Original Message -----
| From: "Ron Williams" <ronwilliams32@c...>
| To: "ASP Databases" <asp_databases@p...>
| Sent: Friday, January 10, 2003 3:53 PM
| Subject: [asp_databases] Re: Date Time Select Problem
|
|
| | if you are using a variable, use cDate(some-variable).  ASP will make
all
| | variables text no matter where it comes from, so convert it first, then
| | try the comparison and see how that works.  even if your variable hold
| | 3/2/00 or something like that, it needs to be converted.
| |
| | in case you didnt know: cDate is a function that converts values to
| dates.
| |
| | if it doesnt work, send the code and ill try to re-produce the problem.
| |
| | let me know
| |
| | ron
| |
| | **IT'S HARD TO HELP WITHOUT CODE GUYS/GIRLS!!!!**
| |
| | > I've searched the list archives and just don't see what I'm looking
| for.
| | I'm trying to do a
| | Select on records against a Date/Time field where the date is sometime
| | within the last 30 days.
| | I don't care about the time of day. So I have:
| |
| | WHERE DateTimeField > some-variable
| |
| | Exactly how do I set this up? Thanks in advance for your reply.
| |
| |
| | ~8^) Pat Wong (ICQ #61070813)
| | http://www.napathon.com/
| | -------------------------------------------------------------------
| | Senility Prayer:
| | God grant me the senility to forget the people I never
| | liked anyway, the good fortune to run into the ones I
| | like, and the eyesight to tell the difference.
| | -------------------------------------------------------------------
| |
| |
|
|

One thing first, the type of database is Access.  So now i am sure your 
query was ok.  but there is your problem.  how are you 
populating "DateAdded"?  it is obviously not what you wanted to do.  you 
need to show us the code you use to put data in DateAdded.  if it is a 
field from a database, then you know you must retrieve the value from the 
database and assign the value to a variable.  if it is coming from a form, 
you need to use Request.Form or Request.QueryString to get the value.

but i cant say until i see the code.


> Sorry to be so dense, but I just don't understand why my code won't 
work. I've looked at the
DateAdded field in MS Access, and it definitely is being populated. Yet 
when my ASP page loads,
it's selecting every single record from the database. Furthermore, when I 
add the following
statement

Response.Write "Date Added = " & DateAdded

it displays like this:

Date Added 

It's like it isn't there! What am I missing?

----- Original Message -----
From: "Ron Williams" <ronwilliams32@c...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, January 11, 2003 7:33 PM
Subject: [asp_databases] Re: Date Time Select Problem


| The answer is yes you can.  How?  exactly how you are doing it.  the 
built-
| in function should come on the ASP side, not the database side,
| assuming "selectdate" is a variable on your ASP page.  just make sure
| selectdate is a date, and you do that by converting it to a date, for
| example where dateadded > cDate(selectdate).
|
| i am also assuming you are using ADO, ASP, and VBScript.  if not, than
| sorry, i cant help much.
|
| ron
|
|
| > I guess maybe I wasn't too clear about what I'm trying to do. I'm 
trying
| to filter records
| using the Select statement. I know that I can probably select all the
| records, parse out the
| date from the DateAdded (date/time) field and compare the date in the
| database to the date
| range I'm looking for to see whether to accept or reject the record.
|
| I should also point out that modifying the database to have date and time
| as separate fields
| isn't an option. The database I'm using is an Access backend to some
| purchased software.
|
| I'd like to filter them using the Select statement instead. Here's what I
| came up with, but it
| doesn't work.
|
| SelectDate = Date - 31
|
| SELECT tblAlbums.Title AS title,
| tblAlbums.MediaType AS format,
| tblAlbums.AlbumID AS albumid,
| tblAlbums.Genre AS genre,
| tblAlbums.UserField1 as userfield1,
| tblAlbums.DateAdded,
| tblArtists.The AS artist_the,
| tblArtists.Artist AS artist,
| tblArtists.ArtistID as artistid,
| tblArtists.SortName AS sortname
| FROM tblArtists INNER JOIN tblAlbums ON tblArtists.ArtistID 
| tblAlbums.ArtistID
| WHERE DateAdded > SelectDate
| ORDER BY sortname,title;
|
| DateAdded is the date/time field I was talking about. I stuck a
| Response.Write in the code to
| see what value was in SelectDate (which was 12/9/2002 12:00:00 AM).  So
| the question is - Is
| there a way to filter records using the Select statement on a date/time
| field? If so, how?
|
| ----- Original Message -----
| From: "Ron Williams" <ronwilliams32@c...>
| To: "ASP Databases" <asp_databases@p...>
| Sent: Friday, January 10, 2003 3:53 PM
| Subject: [asp_databases] Re: Date Time Select Problem
|
|
| | if you are using a variable, use cDate(some-variable).  ASP will make 
all
| | variables text no matter where it comes from, so convert it first, then
| | try the comparison and see how that works.  even if your variable hold
| | 3/2/00 or something like that, it needs to be converted.
| |
| | in case you didnt know: cDate is a function that converts values to
| dates.
| |
| | if it doesnt work, send the code and ill try to re-produce the problem.
| |
| | let me know
| |
| | ron
| |
| | **IT'S HARD TO HELP WITHOUT CODE GUYS/GIRLS!!!!**
| |
| | > I've searched the list archives and just don't see what I'm looking
| for.
| | I'm trying to do a
| | Select on records against a Date/Time field where the date is sometime
| | within the last 30 days.
| | I don't care about the time of day. So I have:
| |
| | WHERE DateTimeField > some-variable
| |
| | Exactly how do I set this up? Thanks in advance for your reply.
| |
| |
| | ~8^) Pat Wong (ICQ #61070813)
| | http://www.napathon.com/
| | -------------------------------------------------------------------
| | Senility Prayer:
| | God grant me the senility to forget the people I never
| | liked anyway, the good fortune to run into the ones I
| | like, and the eyesight to tell the difference.
| | -------------------------------------------------------------------
| |
| |
|
|

Didn't work, but you set me on a path that eventually did work. Here's how to set up the query:

SELECT tblAlbums.Title AS title,
tblAlbums.MediaType AS format,
tblAlbums.AlbumID AS albumid,
tblAlbums.Genre AS genre,
tblAlbums.UserField1 as userfield1,
tblAlbums.DateAdded as DateAdded,
tblArtists.The AS artist_the,
tblArtists.Artist AS artist,
tblArtists.ArtistID as artistid,
tblArtists.SortName AS sortname FROM tblArtists
INNER JOIN tblAlbums ON tblArtists.ArtistID = tblAlbums.ArtistID
WHERE DateAdded > #12/12/2002#
ORDER BY DateAdded;


As you can see, putting the hash marks before and after the select date finally did the trick!
Thanks to all who have offered suggestions.

Not that it matters at this point, but to answer Ron's question as to how DateAdded was being
populated, that's something which is being done automatically by the software using the Access
database.


----- Original Message -----
From: "bwarehouse" <bwarehouse@y...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, January 11, 2003 3:16 PM
Subject: [asp_databases] Re: Date Time Select Problem


| Try this: it may or may not work:
|
|
| Response.Write "Date Added = " & "#" & DateAdded & "#"
|
| Later,
| bware
|
| -----Original Message-----
| From: Pat Wong [mailto:vinyl-junkie@n...]
| Sent: Saturday, January 11, 2003 2:57 PM
| To: ASP Databases
| Subject: [asp_databases] Re: Date Time Select Problem
|
| Sorry to be so dense, but I just don't understand why my code won't work.
| I've looked at the
| DateAdded field in MS Access, and it definitely is being populated. Yet when
| my ASP page loads,
| it's selecting every single record from the database. Furthermore, when I
| add the following
| statement
|
| Response.Write "Date Added = " & DateAdded
|
| it displays like this:
|
| Date Added 
|
| It's like it isn't there! What am I missing?
|
| ----- Original Message -----
| From: "Ron Williams" <ronwilliams32@c...>
| To: "ASP Databases" <asp_databases@p...>
| Sent: Saturday, January 11, 2003 7:33 PM
| Subject: [asp_databases] Re: Date Time Select Problem
|
|
| | The answer is yes you can.  How?  exactly how you are doing it.  the
| built-
| | in function should come on the ASP side, not the database side,
| | assuming "selectdate" is a variable on your ASP page.  just make sure
| | selectdate is a date, and you do that by converting it to a date, for
| | example where dateadded > cDate(selectdate).
| |
| | i am also assuming you are using ADO, ASP, and VBScript.  if not, than
| | sorry, i cant help much.
| |
| | ron
| |
| |
| | > I guess maybe I wasn't too clear about what I'm trying to do. I'm trying
| | to filter records
| | using the Select statement. I know that I can probably select all the
| | records, parse out the
| | date from the DateAdded (date/time) field and compare the date in the
| | database to the date
| | range I'm looking for to see whether to accept or reject the record.
| |
| | I should also point out that modifying the database to have date and time
| | as separate fields
| | isn't an option. The database I'm using is an Access backend to some
| | purchased software.
| |
| | I'd like to filter them using the Select statement instead. Here's what I
| | came up with, but it
| | doesn't work.
| |
| | SelectDate = Date - 31
| |
| | SELECT tblAlbums.Title AS title,
| | tblAlbums.MediaType AS format,
| | tblAlbums.AlbumID AS albumid,
| | tblAlbums.Genre AS genre,
| | tblAlbums.UserField1 as userfield1,
| | tblAlbums.DateAdded,
| | tblArtists.The AS artist_the,
| | tblArtists.Artist AS artist,
| | tblArtists.ArtistID as artistid,
| | tblArtists.SortName AS sortname
| | FROM tblArtists INNER JOIN tblAlbums ON tblArtists.ArtistID 
| | tblAlbums.ArtistID
| | WHERE DateAdded > SelectDate
| | ORDER BY sortname,title;
| |
| | DateAdded is the date/time field I was talking about. I stuck a
| | Response.Write in the code to
| | see what value was in SelectDate (which was 12/9/2002 12:00:00 AM).  So
| | the question is - Is
| | there a way to filter records using the Select statement on a date/time
| | field? If so, how?
| |
| | ----- Original Message -----
| | From: "Ron Williams" <ronwilliams32@c...>
| | To: "ASP Databases" <asp_databases@p...>
| | Sent: Friday, January 10, 2003 3:53 PM
| | Subject: [asp_databases] Re: Date Time Select Problem
| |
| |
| | | if you are using a variable, use cDate(some-variable).  ASP will make
| all
| | | variables text no matter where it comes from, so convert it first, then
| | | try the comparison and see how that works.  even if your variable hold
| | | 3/2/00 or something like that, it needs to be converted.
| | |
| | | in case you didnt know: cDate is a function that converts values to
| | dates.
| | |
| | | if it doesnt work, send the code and ill try to re-produce the problem.
| | |
| | | let me know
| | |
| | | ron
| | |
| | | **IT'S HARD TO HELP WITHOUT CODE GUYS/GIRLS!!!!**
| | |
| | | > I've searched the list archives and just don't see what I'm looking
| | for.
| | | I'm trying to do a
| | | Select on records against a Date/Time field where the date is sometime
| | | within the last 30 days.
| | | I don't care about the time of day. So I have:
| | |
| | | WHERE DateTimeField > some-variable
| | |
| | | Exactly how do I set this up? Thanks in advance for your reply.
| | |
| | |
| | | ~8^) Pat Wong (ICQ #61070813)
| | | http://www.napathon.com/
| | | -------------------------------------------------------------------
| | | Senility Prayer:
| | | God grant me the senility to forget the people I never
| | | liked anyway, the good fortune to run into the ones I
| | | like, and the eyesight to tell the difference.
| | | -------------------------------------------------------------------
| | |
| | |
| |
| |
|
|
|
|
|
|

No problem.

Later,
bware

-----Original Message-----
From: Pat Wong [mailto:vinyl-junkie@n...]
Sent: Saturday, January 11, 2003 8:46 PM
To: ASP Databases
Subject: [asp_databases] Re: Date Time Select Problem

Didn't work, but you set me on a path that eventually did work. Here's how
to set up the query:

SELECT tblAlbums.Title AS title,
tblAlbums.MediaType AS format,
tblAlbums.AlbumID AS albumid,
tblAlbums.Genre AS genre,
tblAlbums.UserField1 as userfield1,
tblAlbums.DateAdded as DateAdded,
tblArtists.The AS artist_the,
tblArtists.Artist AS artist,
tblArtists.ArtistID as artistid,
tblArtists.SortName AS sortname FROM tblArtists
INNER JOIN tblAlbums ON tblArtists.ArtistID = tblAlbums.ArtistID
WHERE DateAdded > #12/12/2002#
ORDER BY DateAdded;


As you can see, putting the hash marks before and after the select date
finally did the trick!
Thanks to all who have offered suggestions.

Not that it matters at this point, but to answer Ron's question as to how
DateAdded was being
populated, that's something which is being done automatically by the
software using the Access
database.


----- Original Message -----
From: "bwarehouse" <bwarehouse@y...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, January 11, 2003 3:16 PM
Subject: [asp_databases] Re: Date Time Select Problem


| Try this: it may or may not work:
|
|
| Response.Write "Date Added = " & "#" & DateAdded & "#"
|
| Later,
| bware
|
| -----Original Message-----
| From: Pat Wong [mailto:vinyl-junkie@n...]
| Sent: Saturday, January 11, 2003 2:57 PM
| To: ASP Databases
| Subject: [asp_databases] Re: Date Time Select Problem
|
| Sorry to be so dense, but I just don't understand why my code won't work.
| I've looked at the
| DateAdded field in MS Access, and it definitely is being populated. Yet
when
| my ASP page loads,
| it's selecting every single record from the database. Furthermore, when I
| add the following
| statement
|
| Response.Write "Date Added = " & DateAdded
|
| it displays like this:
|
| Date Added 
|
| It's like it isn't there! What am I missing?
|
| ----- Original Message -----
| From: "Ron Williams" <ronwilliams32@c...>
| To: "ASP Databases" <asp_databases@p...>
| Sent: Saturday, January 11, 2003 7:33 PM
| Subject: [asp_databases] Re: Date Time Select Problem
|
|
| | The answer is yes you can.  How?  exactly how you are doing it.  the
| built-
| | in function should come on the ASP side, not the database side,
| | assuming "selectdate" is a variable on your ASP page.  just make sure
| | selectdate is a date, and you do that by converting it to a date, for
| | example where dateadded > cDate(selectdate).
| |
| | i am also assuming you are using ADO, ASP, and VBScript.  if not, than
| | sorry, i cant help much.
| |
| | ron
| |
| |
| | > I guess maybe I wasn't too clear about what I'm trying to do. I'm
trying
| | to filter records
| | using the Select statement. I know that I can probably select all the
| | records, parse out the
| | date from the DateAdded (date/time) field and compare the date in the
| | database to the date
| | range I'm looking for to see whether to accept or reject the record.
| |
| | I should also point out that modifying the database to have date and
time
| | as separate fields
| | isn't an option. The database I'm using is an Access backend to some
| | purchased software.
| |
| | I'd like to filter them using the Select statement instead. Here's what
I
| | came up with, but it
| | doesn't work.
| |
| | SelectDate = Date - 31
| |
| | SELECT tblAlbums.Title AS title,
| | tblAlbums.MediaType AS format,
| | tblAlbums.AlbumID AS albumid,
| | tblAlbums.Genre AS genre,
| | tblAlbums.UserField1 as userfield1,
| | tblAlbums.DateAdded,
| | tblArtists.The AS artist_the,
| | tblArtists.Artist AS artist,
| | tblArtists.ArtistID as artistid,
| | tblArtists.SortName AS sortname
| | FROM tblArtists INNER JOIN tblAlbums ON tblArtists.ArtistID 
| | tblAlbums.ArtistID
| | WHERE DateAdded > SelectDate
| | ORDER BY sortname,title;
| |
| | DateAdded is the date/time field I was talking about. I stuck a
| | Response.Write in the code to
| | see what value was in SelectDate (which was 12/9/2002 12:00:00 AM).  So
| | the question is - Is
| | there a way to filter records using the Select statement on a date/time
| | field? If so, how?
| |
| | ----- Original Message -----
| | From: "Ron Williams" <ronwilliams32@c...>
| | To: "ASP Databases" <asp_databases@p...>
| | Sent: Friday, January 10, 2003 3:53 PM
| | Subject: [asp_databases] Re: Date Time Select Problem
| |
| |
| | | if you are using a variable, use cDate(some-variable).  ASP will make
| all
| | | variables text no matter where it comes from, so convert it first,
then
| | | try the comparison and see how that works.  even if your variable hold
| | | 3/2/00 or something like that, it needs to be converted.
| | |
| | | in case you didnt know: cDate is a function that converts values to
| | dates.
| | |
| | | if it doesnt work, send the code and ill try to re-produce the
problem.
| | |
| | | let me know
| | |
| | | ron
| | |
| | | **IT'S HARD TO HELP WITHOUT CODE GUYS/GIRLS!!!!**
| | |
| | | > I've searched the list archives and just don't see what I'm looking
| | for.
| | | I'm trying to do a
| | | Select on records against a Date/Time field where the date is sometime
| | | within the last 30 days.
| | | I don't care about the time of day. So I have:
| | |
| | | WHERE DateTimeField > some-variable
| | |
| | | Exactly how do I set this up? Thanks in advance for your reply.
| | |
| | |
| | | ~8^) Pat Wong (ICQ #61070813)
| | | http://www.napathon.com/
| | | -------------------------------------------------------------------
| | | Senility Prayer:
| | | God grant me the senility to forget the people I never
| | | liked anyway, the good fortune to run into the ones I
| | | like, and the eyesight to tell the difference.
| | | -------------------------------------------------------------------
| | |
| | |
| |
| |
|
|
|
|
|
|

With access 2000,  you'll use the DateAdd() function like this:

SELECT fldname FROM tblname WHERE DateAdded >= DateAdd('d', -30, Now())

-Kim




-----Original Message-----
From: Pat Wong [mailto:vinyl-junkie@n...]
Sent: 11. januar 2003 16:38
To: ASP Databases
Subject: [asp_databases] RE: Date Time Select Problem


The database I'm using is called CD Trustee. As I said in my original post,
it uses MS Access
as a back end to the software.  If you're interested you can read about CD
Trustee here:
http://www.base40.com/

You're correct that it has several built-in functions. However, the only
built-in function that
has anything to do with a date at all is listing albums by year. That's not
the field I need
for my query. In fact, there is nothing I can see that's already built into
the database that
would get me where I want to go.  :o(

So I am still left with my original question - I want to Select all albums
that were put into
the database within the last 30 days. How do I set up my Select statement to
filter based on a
date/time field? Is it even possible to do that?

----- Original Message -----
From: "Kim Iwan Hansen" <kimiwan@k...>
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, January 10, 2003 8:17 AM
Subject: [asp_databases] RE: Date Time Select Problem


| What database are you using? - I'm sure it'll have some inbuilt function,
| but how that function works is different from database to database.
|
| -Kim
|
| -----Original Message-----
| From: Pat Wong [mailto:vinyl-junkie@n...]
| Sent: 10. januar 2003 04:33
| To: ASP Databases
| Subject: [asp_databases] Date Time Select Problem
|
|
| I've searched the list archives and just don't see what I'm looking for.
I'm
| trying to do a
| Select on records against a Date/Time field where the date is sometime
| within the last 30 days.
| I don't care about the time of day. So I have:
|
| WHERE DateTimeField > some-variable
|
| Exactly how do I set this up? Thanks in advance for your reply.
|
|
| ~8^) Pat Wong (ICQ #61070813)
| http://www.napathon.com/
| -------------------------------------------------------------------
| Senility Prayer:
| God grant me the senility to forget the people I never
| liked anyway, the good fortune to run into the ones I
| like, and the eyesight to tell the difference.
| -------------------------------------------------------------------
|
|
|
|
|
|

Thanks for the tip. I'll file this one away for future reference.



| With access 2000,  you'll use the DateAdd() function like this:
| 
| SELECT fldname FROM tblname WHERE DateAdded >= DateAdd('d', -30, Now())
| 
| -Kim
| 
| -----Original Message-----
| 
(snip)
| 
| I want to Select all albums
| that were put into
| the database within the last 30 days. 

~8^) Pat Wong (ICQ #61070813)
http://www.napathon.com/
-------------------------------------------------------------------
Senility Prayer:
God grant me the senility to forget the people I never
liked anyway, the good fortune to run into the ones I
like, and the eyesight to tell the difference. 
-------------------------------------------------------------------

you are accomplishing the task by send a hard coded value.  there is no 
reason that you cant use a variable that is popoulated from a selection by 
a user on a web form.  i have done it many times.  the fact that yourdate 
had nothing in it, indicates that your problem was retrieving the value 
from the form, if that is what you were using.



> Thanks for the tip. I'll file this one away for future reference.



| With access 2000,  you'll use the DateAdd() function like this:
| 
| SELECT fldname FROM tblname WHERE DateAdded >= DateAdd('d', -30, Now())
| 
| -Kim
| 
| -----Original Message-----
| 
(snip)
| 
| I want to Select all albums
| that were put into
| the database within the last 30 days. 

~8^) Pat Wong (ICQ #61070813)
http://www.napathon.com/
-------------------------------------------------------------------
Senility Prayer:
God grant me the senility to forget the people I never
liked anyway, the good fortune to run into the ones I
like, and the eyesight to tell the difference. 
-------------------------------------------------------------------

Actually, I was retrieving it from the database, not a form. I could see it was populated, yet
it wasn't displaying on a Response.Write statement. I have no idea why, and thankfully I'm not
trying to display on my web page.



| you are accomplishing the task by send a hard coded value.  there is no
| reason that you cant use a variable that is popoulated from a selection by
| a user on a web form.  i have done it many times.  the fact that yourdate
| had nothing in it, indicates that your problem was retrieving the value
| from the form, if that is what you were using.
|
|
|
| > Thanks for the tip. I'll file this one away for future reference.
|
|
|
| | With access 2000,  you'll use the DateAdd() function like this:
| |
| | SELECT fldname FROM tblname WHERE DateAdded >= DateAdd('d', -30, Now())
| |
| | -Kim
| |
| | -----Original Message-----
| |
| (snip)
| |
| | I want to Select all albums
| | that were put into
| | the database within the last 30 days.
|
| ~8^) Pat Wong (ICQ #61070813)
| http://www.napathon.com/
| -------------------------------------------------------------------
| Senility Prayer:
| God grant me the senility to forget the people I never
| liked anyway, the good fortune to run into the ones I
| like, and the eyesight to tell the difference.
| -------------------------------------------------------------------
|

ok.  well you have it working now.  remember, when you retrieve it from 
the database, you can only write the value as objRS("DateAdded"), unless 
you assign that value to a variable...

theVariable = objRS("DateAdded")

then 

response.write theVariable

also, objRS("DateAdded") will return the value of the DateAdded field for 
the current record only.  you will have to iterate thru the record set 
with a loop to get all of the DateAdded values.  


> Actually, I was retrieving it from the database, not a form. I could see 
it was populated, yet
it wasn't displaying on a Response.Write statement. I have no idea why, 
and thankfully I'm not
trying to display on my web page.



| you are accomplishing the task by send a hard coded value.  there is no
| reason that you cant use a variable that is popoulated from a selection 
by
| a user on a web form.  i have done it many times.  the fact that yourdate
| had nothing in it, indicates that your problem was retrieving the value
| from the form, if that is what you were using.
|
|
|
| > Thanks for the tip. I'll file this one away for future reference.
|
|
|
| | With access 2000,  you'll use the DateAdd() function like this:
| |
| | SELECT fldname FROM tblname WHERE DateAdded >= DateAdd('d', -30, Now())
| |
| | -Kim
| |
| | -----Original Message-----
| |
| (snip)
| |
| | I want to Select all albums
| | that were put into
| | the database within the last 30 days.
|
| ~8^) Pat Wong (ICQ #61070813)
| http://www.napathon.com/
| -------------------------------------------------------------------
| Senility Prayer:
| God grant me the senility to forget the people I never
| liked anyway, the good fortune to run into the ones I
| like, and the eyesight to tell the difference.
| -------------------------------------------------------------------
|

Ah, yes. Now that I think of it, I should have known better than to try and display it the way
I was doing it. Thanks for the reminder. Um, I only started learning ASP last August. How long
can I blame this lack of knowledge on being a newbie?   ;o)



| ok.  well you have it working now.  remember, when you retrieve it from
| the database, you can only write the value as objRS("DateAdded"), unless
| you assign that value to a variable...
|
| theVariable = objRS("DateAdded")
|
| then
|
| response.write theVariable
|
| also, objRS("DateAdded") will return the value of the DateAdded field for
| the current record only.  you will have to iterate thru the record set
| with a loop to get all of the DateAdded values.
|
|
| > Actually, I was retrieving it from the database, not a form. I could see
| it was populated, yet
| it wasn't displaying on a Response.Write statement. I have no idea why,
| and thankfully I'm not
| trying to display on my web page.
|
|
|
| | you are accomplishing the task by send a hard coded value.  there is no
| | reason that you cant use a variable that is popoulated from a selection
| by
| | a user on a web form.  i have done it many times.  the fact that yourdate
| | had nothing in it, indicates that your problem was retrieving the value
| | from the form, if that is what you were using.
| |
| |
| |
| | > Thanks for the tip. I'll file this one away for future reference.
| |
| |
| |
| | | With access 2000,  you'll use the DateAdd() function like this:
| | |
| | | SELECT fldname FROM tblname WHERE DateAdded >= DateAdd('d', -30, Now())
| | |
| | | -Kim
| | |
| | | -----Original Message-----
| | |
| | (snip)
| | |
| | | I want to Select all albums
| | | that were put into
| | | the database within the last 30 days.
| |
| | ~8^) Pat Wong (ICQ #61070813)
| | http://www.napathon.com/
| | -------------------------------------------------------------------
| | Senility Prayer:
| | God grant me the senility to forget the people I never
| | liked anyway, the good fortune to run into the ones I
| | like, and the eyesight to tell the difference.
| | -------------------------------------------------------------------
| |
|
|

HaHaHa, the good thing is you tried the response.write in the first 
place.  that is a good debugging tool, although it is not usually promoted 
as such.  dont ignore it though, if the data is not there, figure it out.  
there can only be a limited number of reasons.

> Ah, yes. Now that I think of it, I should have known better than to try 
and display it the way
I was doing it. Thanks for the reminder. Um, I only started learning ASP 
last August. How long
can I blame this lack of knowledge on being a newbie?   ;o)



| ok.  well you have it working now.  remember, when you retrieve it from
| the database, you can only write the value as objRS("DateAdded"), unless
| you assign that value to a variable...
|
| theVariable = objRS("DateAdded")
|
| then
|
| response.write theVariable
|
| also, objRS("DateAdded") will return the value of the DateAdded field for
| the current record only.  you will have to iterate thru the record set
| with a loop to get all of the DateAdded values.
|
|
| > Actually, I was retrieving it from the database, not a form. I could 
see
| it was populated, yet
| it wasn't displaying on a Response.Write statement. I have no idea why,
| and thankfully I'm not
| trying to display on my web page.
|
|
|
| | you are accomplishing the task by send a hard coded value.  there is no
| | reason that you cant use a variable that is popoulated from a selection
| by
| | a user on a web form.  i have done it many times.  the fact that 
yourdate
| | had nothing in it, indicates that your problem was retrieving the value
| | from the form, if that is what you were using.
| |
| |
| |
| | > Thanks for the tip. I'll file this one away for future reference.
| |
| |
| |
| | | With access 2000,  you'll use the DateAdd() function like this:
| | |
| | | SELECT fldname FROM tblname WHERE DateAdded >= DateAdd('d', -30, Now
())
| | |
| | | -Kim
| | |
| | | -----Original Message-----
| | |
| | (snip)
| | |
| | | I want to Select all albums
| | | that were put into
| | | the database within the last 30 days.
| |
| | ~8^) Pat Wong (ICQ #61070813)
| | http://www.napathon.com/
| | -------------------------------------------------------------------
| | Senility Prayer:
| | God grant me the senility to forget the people I never
| | liked anyway, the good fortune to run into the ones I
| | like, and the eyesight to tell the difference.
| | -------------------------------------------------------------------
| |
|
|