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
  • 704,970
  • 168
  • 1,303
  • 1,425
Sivajith
  • 1,141
  • 5
  • 18
  • 36
  • 1
    Why are you converting `datetime` to strings for comparison? [Bad habits to kick : mis-handling date / range queries - Aaron Bertrand](http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx) [What do `between` and the devil have in common? - Aaron Bertrand](http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx) – 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
  • 30,929
  • 7
  • 33
  • 51
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,171
  • 7
  • 17