Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Select records within a date range

Posted on 2003-11-13
10
Medium Priority
?
1,680 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

636 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