?
Solved

Select records within a date range

Posted on 2003-11-13
10
Medium Priority
?
1,684 Views
Last Modified: 2012-08-13
I am using VBScript ASP and SQLServer and would like to select a count of records entered into the db between today and yesterday, e.g. select count(id) from table where time_entered >= yesterday and <= today.

My problem seems to be with the dates themselves. I have tried using VB Script now() and SQLServer getDate() but can't seem to make anything work!  
0
Comment
Question by:Ellmb122
  • 5
  • 5
10 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 9743507
Remember that the time is included as part of the date, so you must set the time properly.  If, for example, you want everything from 12:01am yesterday to the current time, try something like this:

SELECT COUNT(id)
FROM yourTable
WHERE time_entered BETWEEN CAST(CONVERT(CHAR(10), DATEADD(DAY, -1, GETDATE()), 120) AS DATETIME) AND GETDATE()
0
 

Author Comment

by:Ellmb122
ID: 9743556
This is the exact code I am now using:
SQLQuery11 = "SELECT count(response_id) as last_hour from responses where time_sent between CAST(CONVERT(CHAR(10), DATEADD(DAY, -1, GETDATE()), 120) AS DATETIME) AND GETDATE());"

Set RSresponses_last_hour = OBJdbConn.Execute(SQLQuery11)
responses_last_hour = RSresponses_last_hour("last_hour")

RSresponses_last_hour.close
Set RSresponses_last_hour = nothing

It doesn't work :(
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9743596
Is it a syntax error?  You added a right paren to the end of the statement that should not be there (unless it's needed for VB).

Try the command in Query Analyzer and see if it works.

Also, verify that you have some data in the table that will match the query.

Finally, you can run this in QA to see the date range that will be selected:

SELECT CAST(CONVERT(CHAR(10), DATEADD(DAY, -1, GETDATE()), 120) AS DATETIME), GETDATE()
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Ellmb122
ID: 9743625
Yes it was a syntax error, sorry. It works now thanks.

Could you please explain the code a little e.g. what does 120 do? And how easily can the code be adapted for the last hour, last 10 minutes etc?

Your help is much appreciated.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9743684
Sure.

120 is format yyyy-mm-dd.  (You can find all the format codes and their meanings in Books Online under topic "CAST and CONVERT".).  Since time is not included in that format, it will default to midnight (00:00).

So, step by step, this code:

CAST(CONVERT(CHAR(10), DATEADD(DAY, -1, GETDATE()), 120) AS DATETIME)

1) subtracts a day from the current date and time (time is still there for the moment)
2) reformats the date and time as yyyy-mm-dd only -- the length of 10 forces SQL to truncate the date time which follows
3) the string yyyy-mm-dd is converted to a datetime, and since a specific time is not given, it defaults to midnight.  If you prefer, for clarity you could add a time string of midnight to the time yourself, for example:
CAST(CONVERT(CHAR(10), DATEADD(DAY, -1, GETDATE()), 120) + ' 00:00:00.000' AS DATETIME)
This might be clearer to someone who has to read the coded.

I use 120 (yyyy-mm-dd) format because it is recognized regardless of server date settings.  For example, in America dates are usually specified mm/dd/[yy]yy, but in Europe it's often dd/mm/[yy]yy, so a mm/dd/yyyy can sometimes be misinterpreted.  But SQL *always* correctly recognizes a format of yyyy-mm-dd to be mm and dd, never dd and mm (yyyy-dd-mm is not allowed).
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9743691
CORRECTION:

>> Since time is not included in that format, it will default to midnight (00:00).

Clearly format 120 does include a time, but I meant that in context of the code used, where a length of time was used to force the time not to be included.
0
 

Author Comment

by:Ellmb122
ID: 9743739
Ok thanks. The points are yours.

Sorry to be a pain but how should I change that code to get it to select records from the last 60 minutes?

Also, I've looked at the different date format codes at http://www.databasejournal.com/features/mssql/article.php/1469841 and this seems to think that 120 includes the time?
0
 

Author Comment

by:Ellmb122
ID: 9743896
Thanks for your help. I would still be interested to know how to select records from the last 60 mins if possible?
0
 

Author Comment

by:Ellmb122
ID: 9743936
I've opened up a new post with another 500 up for grabs.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9748055
The last 60 minutes is kinda' easy once you're familiar with DATEADD:

WHERE time_entered BETWEEN DATEADD(HOUR, -1, GETDATE()) AND GETDATE()
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question