• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

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
0
team2005
Asked:
team2005
1 Solution
 
sammySeltzerCommented:
Maybe, you can try CASTING the compare date:

select * from TransactionTable
where  CAST(CreatedDate as datetime) between '2013/4/4' and '2014/4/4'

Open in new window

0
 
Scott PletcherSenior DBACommented:
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").
0
 
awking00Commented:
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.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
team2005Author Commented:
Hi!

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

Dosent include 2013-12-20 ?
0
 
awking00Commented:
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.
0
 
team2005Author Commented:
thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now