0

I am working with an application (EHR system) and needs to bring records with in the specific date range in SSRS record. In the script I format the date variable and applies to the column.

Convert(varchar(10), @begdt, 101) + ' 00:00:00' and 
Convert(varchar(10), @enddt, 101) + ' 23:59:59'

If I set the begin and end date to 01/01/2017 and 01/31/2017, then it pulls the records that are associated with 02/01/2017. Can anyone help me with this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sivajith
  • 1,181
  • 5
  • 19
  • 38
  • 1
    Why are you converting `datetime` to strings for comparison? [Bad habits to kick : mis-handling date / range queries - Aaron Bertrand](https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries) [What do `between` and the devil have in common? - Aaron Bertrand](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) – SqlZim Oct 04 '17 at 14:57

3 Answers3

0

Don't convert the dates to strings. When you do, you allow SQL Server to compare them alphabetically, rather than in actual date order.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
0

Perhaps you have a language conflict (US vs UK) and SQL Server is in fact seeing that date as 2nd of January. This post offers info on Language and Dates: (SQL Server Datetime issues. American vs. British?)

Stephen Whitfield
  • 81
  • 6
0

Don't use between, and set your date formats different:

@begdt = '20170101'
@enddt = '20170131'

WHERE column1 >= @begdt AND column2 < @enddt

This will take care of your datetime issue

Simon
  • 1,201
  • 9
  • 18