Problems using datediff in SQL with ASP

[stephenmcnutt]

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.

[fahdmalik]

try this:

SELECT *
FROM Results
WHERE DATEDIFF(hh,Timestamp,GetDate())<=16
ORDER BY line ASC,Timestamp ASC;

[markrawlingson]

SELECT *
FROM Results
WHERE DATEDIFF(hh,Result.Timestamp,GetDate())<=16
ORDER BY line ASC,Timestamp ASC;
 

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.