469,934 Members | 2,763 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

Problems using datediff in SQL with ASP

I'm trying to do something that should be trivial. I'm a teacher at an elementary school, and I'm setting up an ASP form page for teachers to vote each afternoon on which dismissal line behaved best.

I've done this sort of thing before. For example, I've set up a form page teachers use each day to record lunch counts. The results are saved to a database on the website, and a second ASP page displays the result using this "custom query":

SELECT *
FROM Results
WHERE DatePart('y',Results.Timestamp)=DatePart('y',Now() )
ORDER BY teacherName ASC,Timestamp ASC;

That works fine and returns only records made today. For this new form, however, I need records returned within the last 16 hours or so. For this I've tried to use the datediff function, but I can't get it to work. I've tried:

SELECT *
FROM Results
WHERE DATEDIFF('Hour',Results.Timestamp,Now())<=16
ORDER BY line ASC,Timestamp ASC;

Instead of 'Hour' I've tried "Hour", Hour, 'hh', 'hh", and hh. I've tried getdate() instead of Now().

I know the results page is properly connected with the database because I get results if I don't try using a "custom query". I'm using FrontPage 2003. Thanks in advance.
Oct 16 '07 #1
2 3172
try this:

SELECT *
FROM Results
WHERE DATEDIFF(hh,Timestamp,GetDate())<=16
ORDER BY line ASC,Timestamp ASC;
Oct 16 '07 #2
markrawlingson
346 Expert 100+
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Results
  3. WHERE DATEDIFF(hh,Result.Timestamp,GetDate())<=16
  4. ORDER BY line ASC,Timestamp ASC;
  5.  
Is the correct way to do it for what you want to do.

But, it depends on what the value of Result.Timestamp is. I'm assuming this is a column in your database that gets updated with the date and time when a record is created, but in what format? Is it just the time, the time and date, just the date, the date in dd/mm/yyyy format, the date in mm/dd/yyyy format, the date in dd/mm/yy format, the date in mm/dd/yy format etc etc?? There are many combinations, and each will return a different result.

Your problem is most likely that Result.Timestamp is in a different format than the format in which GetDate() is returned. If this is the case, it will give you an inaccurate number of hours returned, probably much greated than 16, which means the recordset will return no records, or in some cases may even throw an error.

An overview of DateDiff() and exactly HOW it computes the difference in dates

DateDiff computes the difference in dates by FIRST translating both the Start Date and the End Date into an integer representing the number of (minutes, hours, days, years, minutes, milliseconds, etc) since the epoch (00:00:00 GMT, January 1, 1970;), and THEN comparing them.
Oct 16 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

19 posts views Thread by Lauren Quantrell | last post: by
2 posts views Thread by CDF | last post: by
2 posts views Thread by Contro | last post: by
4 posts views Thread by John Ortt | last post: by
3 posts views Thread by Price Brattin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.