|
 |
asp_databases thread: Date Calculations
Message #1 by "Tom Lawton" <tomlawton@f...> on Tue, 20 Feb 2001 14:21:10
|
|
Hello,
I'm looking to remove all records from a database table, that are over 24
hours old, and use information to increment another table.
So far I've started writing a script on an asp admin page that is checked
everyday. When the page is opened I want this script to pull out all the
records over 24 hours old, use the information in these records to
increment another table, and then delete the records.
My main problem is just getting the records that are over 24 hours old.
Is there an easy way to do this?
Message #2 by "Blake, Shane" <Shane.Blake@p...> on Tue, 20 Feb 2001 10:03:05 -0500
|
|
look up the datediff() function in the help or sql books online
ie
select * from table where datediff(h, date1, date2) > 24
shane
-----Original Message-----
From: Tom Lawton [mailto:tomlawton@f...]
Sent: Tuesday, February 20, 2001 9:21 AM
To: ASP Databases
Subject: [asp_databases] Date Calculations
Hello,
I'm looking to remove all records from a database table, that are over 24
hours old, and use information to increment another table.
So far I've started writing a script on an asp admin page that is checked
everyday. When the page is opened I want this script to pull out all the
records over 24 hours old, use the information in these records to
increment another table, and then delete the records.
My main problem is just getting the records that are over 24 hours old.
Is there an easy way to do this?
Message #3 by "Greg Covey" <gecovey@s...> on Tue, 20 Feb 2001 21:47:10 -0800
|
|
datediff isn't a SQL command, it's a VBS function. I don't
think your SQL statement would work.
If you're using the NOW() function to generate the original
timestamps, you could do it like this:
Find out when 24 hours ago is:
date24Ago = DateAdd("h", -24, NOW())
Then, open a recordset based on a SQL string something like
this:
"SELECT datetime FROM yourTable WHERE dateTime <= #" &
date24Ago & "#;"
This should get you all records that are 24 hours or more
old. Note that delimiting the date value in the SQL string
with the hash/pound (#) assumes that you're using Access.
Change it to a single quote (') if you're using MS SQL (I
think).
hth,
Greg Covey
> -----Original Message-----
> From: Blake, Shane [mailto:Shane.Blake@p...]
> Sent: Tuesday, February 20, 2001 7:03 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Date Calculations
>
>
> look up the datediff() function in the help or sql books
online
>
> ie
> select * from table where datediff(h, date1, date2) > 24
>
> shane
>
> -----Original Message-----
> From: Tom Lawton [mailto:tomlawton@f...]
> Sent: Tuesday, February 20, 2001 9:21 AM
> To: ASP Databases
> Subject: [asp_databases] Date Calculations
>
>
> Hello,
>
> I'm looking to remove all records from a database table,
that
> are over 24
> hours old, and use information to increment another table.
>
> So far I've started writing a script on an asp admin page
> that is checked
> everyday. When the page is opened I want this script to
pull
> out all the
> records over 24 hours old, use the information in these
records to
> increment another table, and then delete the records.
>
> My main problem is just getting the records that are over
24
> hours old.
>
> Is there an easy way to do this?
>
>
>
>
Message #4 by "Tom Lawton" <tomlawton@f...> on Wed, 21 Feb 2001 10:25:49
|
|
Hello,
Thanks a lot I've now got it working!
Cheers
TOM
Message #5 by "Blake, Shane" <Shane.Blake@p...> on Wed, 21 Feb 2001 08:56:14 -0500
|
|
this is right out of sql7 books online...
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
datediff is not just vbs
fyi...
shane
-----Original Message-----
From: Greg Covey [mailto:gecovey@s...]
Sent: Wednesday, February 21, 2001 12:47 AM
To: ASP Databases
Subject: [asp_databases] RE: Date Calculations
datediff isn't a SQL command, it's a VBS function. I don't
think your SQL statement would work.
If you're using the NOW() function to generate the original
timestamps, you could do it like this:
Find out when 24 hours ago is:
date24Ago = DateAdd("h", -24, NOW())
Then, open a recordset based on a SQL string something like
this:
"SELECT datetime FROM yourTable WHERE dateTime <= #" &
date24Ago & "#;"
This should get you all records that are 24 hours or more
old. Note that delimiting the date value in the SQL string
with the hash/pound (#) assumes that you're using Access.
Change it to a single quote (') if you're using MS SQL (I
think).
hth,
Greg Covey
> -----Original Message-----
> From: Blake, Shane [mailto:Shane.Blake@p...]
> Sent: Tuesday, February 20, 2001 7:03 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Date Calculations
>
>
> look up the datediff() function in the help or sql books
online
>
> ie
> select * from table where datediff(h, date1, date2) > 24
>
> shane
>
> -----Original Message-----
> From: Tom Lawton [mailto:tomlawton@f...]
> Sent: Tuesday, February 20, 2001 9:21 AM
> To: ASP Databases
> Subject: [asp_databases] Date Calculations
>
>
> Hello,
>
> I'm looking to remove all records from a database table,
that
> are over 24
> hours old, and use information to increment another table.
>
> So far I've started writing a script on an asp admin page
> that is checked
> everyday. When the page is opened I want this script to
pull
> out all the
> records over 24 hours old, use the information in these
records to
> increment another table, and then delete the records.
>
> My main problem is just getting the records that are over
24
> hours old.
>
> Is there an easy way to do this?
>
|
|
|
View Cart
