Solved

Select records within a date range

Posted on 2003-11-13
10
1,673 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 69

Accepted Solution

by:
Scott Pletcher earned 500 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 69

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 69

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 69

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 69

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

688 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