asked on

Select records using between...

Hi!

Have two dates that i use in this simple example:

select * from TransactionTable
where CreatedDate between '2013-12-17' and '2013-12-19'

But the problem is that it dosent give me records from 2013-12-19
Only up to -> 2013-12-19

How can i fix this

ASKER CERTIFIED SOLUTION

sammySeltzer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

The foolproof way to do that, no matter what the column data type is, is:

where CreatedDate >= '2013-12-17' and
CreatedDate < '2013-12-20'

Btw, you definitely don't want to use a function on the table column, as that will prevent index seeks from occurring for that column (in technical terms, a function call makes it "NONSARGABLE").

awking00

The problem is likely that you have a time element in your dates. When selecting '2013-12-19' it assumes a time of 00:00:00 or 12 am on the 19th. So any CreatedDate with a time other than midnight on the 19th will not be inclusive in your between clause. The easiest way to get around that is to use the >= and < as ScottPletcher has suggested.

team2005

ASKER

Hi!

where CreatedDate >= '2013-12-17' and
CreatedDate <= '2013-12-20'

Dosent include 2013-12-20 ?

awking00

It would only include midnight on 2013-12-20 and not any createdDate with a time value of greater than or equal to 00:00:01.

team2005

ASKER

thanks