Solved

Select records within a date range

Posted on 2003-11-13
10
1,661 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

759 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

19 Experts available now in Live!

Get 1:1 Help Now