Sql Query not showing data for selected date

Question

I have datetime field in DB, which stores invoice dates (format is 7/15/2014 12:00:00 AM)

Then I have two datetimepickers, for From date and To date.

I am trying to get it through SQL query

>Where TRDate between @DT1 and @DT2 Order By TRRef DESC

The problem is, If I select 7/15/2014 using datetime picker, it does not show the data for 15th. For this I need to select 14th.

Isn't selected date inclusive?

Thanks

Answer 1

Method1:

You should change input parameters like this

SET @DT1 = CONVERT(varchar(11), @DT1 ,101) + ' 00:00:00'

SET @DT2 = CONVERT(varchar(11), @DT2 ,101) + ' 23:59:57';

Then write search query

Or else use this

Method2

Where TRDate between @DT1 and DateAdd(DD,1,@DT2) Order By TRRef DESC

Answer 2

I don't know what RDBMS you are using but here's a simple tip. You can use >= and < for the comparison but you need to add 1 DAY on the second date.

This is not the correct syntax for adding a day in a date. It depends on what RDBMS you used.

WHERE TRDate >= @DT1 AND TRDate < (@DT2 + 1 DAY)
                                        ^ add 1 day

so here's what happened. Assuming you want to get the records between 7/1/2014 12:00:00 AM and 7/15/2014 12:00:00 AM

WHERE TRDate >= '2014-07-01 00:00:00' AND 
      TRDate < '2014-07-16 00:00:00' -- result for adding 1 day on 15th