Date Range Check How To ?

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
jturkingtonAsked:
Who is Participating?
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
Try it this way:
SELECT      COUNT(DISTINCT userid) AS UserIdCount
FROM      USERS
WHERE      date BETWEEN @ReportStartDate AND @ReportEndDate + '23:59:59'
0
 
jturkingtonAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.