Solved

Select records within a date range

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

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
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
 

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:ScottPletcher
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 69

Expert Comment

by:ScottPletcher
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:ScottPletcher
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now