Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 7th, 2006, 07:01 AM
Friend of Wrox
 
Join Date: May 2005
Location: , , .
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default sql date filter

Hi,
I use ms sql.I have a field called "date_created" which has the value "getdate()" by default.
and it returns the full date like this:3/7/2006 1:31:53 AM

I want to filter the records which are added during the past 1 hour
how should I change the sql statement
I have the following line but it returns the records from yesterday up to now.

RsHistory.open "SELECT *,(SELECT COUNT(*) FROM ecard_history WHERE ecard_history.date_created between " & "'" & date()-1 & "'" & " AND " & "'" & date() & "'" & " ) AS Rec_COUNT from ecard_history",Conn,3,3
Reply With Quote
  #2 (permalink)  
Old March 7th, 2006, 10:06 AM
Authorized User
 
Join Date: Mar 2006
Location: , Ohio, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try using DATEPART. You can then perform filtering based on specific parts of the date and time (month, day, hour, minute). It would look something like this (untested):

RsHistory.open "SELECT *,(SELECT COUNT(*) FROM ecard_history WHERE ecard_history.date_created>=DATEPART(hour,GETDATE( ))-1 AS Rec_COUNT from ecard_history",Conn,3,3


Reply With Quote
  #3 (permalink)  
Old March 8th, 2006, 01:13 PM
Friend of Wrox
 
Join Date: May 2005
Location: , , .
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It does not work!
PLEASE give a sample

Reply With Quote
  #4 (permalink)  
Old March 8th, 2006, 10:17 PM
Authorized User
 
Join Date: Mar 2006
Location: , Ohio, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Looks like you're asking to return all the records (SELECT *) and a count of the records (SELECT COUNT(*)), but you had them nested incorrectly. I tested this in Query Analyzer and it returned the data I expected from a test database. Try this:

RsHistory.open "SELECT * FROM ecard_history WHERE ecard_history.date_created>=(DATEPART(hour,GETDATE ())-1)(SELECT COUNT(*) AS Rec_COUNT FROM ecard_history)",Conn,3,3


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help for Select SQL Query with date filter pushpa SQL Server DTS 0 February 6th, 2007 10:06 AM
Filter dataset by Date? spacky001 ASP.NET 2.0 Basics 1 January 23rd, 2007 07:17 PM
Filter form with date fields problem!! chiefouko Access 2 August 14th, 2006 10:15 AM
Macro/Date/Advanced Filter alexyss15 Excel VBA 1 March 30th, 2006 01:06 PM



All times are GMT -4. The time now is 05:49 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.