We help IT Professionals succeed at work.

Date Range Check How To ?

jturkington
jturkington asked
on
Medium Priority
242 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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012

Commented:
Try it this way:
SELECT      COUNT(DISTINCT userid) AS UserIdCount
FROM      USERS
WHERE      date BETWEEN @ReportStartDate AND @ReportEndDate + '23:59:59'
CERTIFIED EXPERT
Top Expert 2012
Commented:
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'

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
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))
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.