?
Solved

Date Range Check How To ?

Posted on 2006-03-23
5
Medium Priority
?
187 Views
Last Modified: 2010-03-19
Having a few issues trying to bring back records that ARE BETWEEN AND INCLUDE the start/end report dates

EG  ReportStartDate (DD/MM/YYY) = 20/03/2006
      ReportEndDate (DD/MM/YYY) = 26/03/2006

The Code below doesnt bring back records on the 26/03/2006 ?!

CREATE PROCEDURE spSelect_Dates
      
      @ReportStartdate      SMALLDATETIME,
      @ReportEnddate        SMALLDATETIME,
AS


SELECT       COUNT(DISTINCT userid) AS UserIdCount
FROM       USERS
WHERE       date BETWEEN @ReportStartDate AND @ReportEndDate


Cheers

JT
0
Comment
Question by:jturkington
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16269592
Try it this way:
SELECT      COUNT(DISTINCT userid) AS UserIdCount
FROM      USERS
WHERE      date BETWEEN @ReportStartDate AND @ReportEndDate + '23:59:59'
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1200 total points
ID: 16269622
Actually since you are using smalldatetime and there are no seconds use this:

SELECT      COUNT(DISTINCT userid) AS UserIdCount
FROM      USERS
WHERE      date BETWEEN @ReportStartDate AND @ReportEndDate + '23:59'
0
 

Author Comment

by:jturkington
ID: 16269655
DATE FIELD is also SMALLDATETIME

AND

ReportStartDate/ReportEndDate will always be passed in as (YYYY-MM-DD)

Is BETWEEN the best syntax to use so i maybe use => =< ??

Cheers

JT

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16269712
jturkington,
> The Code below doesnt bring back records on the 26/03/2006 ?!
when u specify this, it looks for the values which are less than or equal '26/03/2006 00:00 am
so any values after tahat it won't show
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 800 total points
ID: 16270294
To avoid possible issues if datetime type is changed, from full to small or small to full, you can use this style:

WHERE      date >= @ReportStartDate AND date < DATEADD(DAY, 1, CONVERT(CHAR(8), @ReportEndDate, 112))
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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 ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

830 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