Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select records using between...

Posted on 2014-04-01
6
Medium Priority
?
150 Views
Last Modified: 2014-04-30
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
Comment
Question by:team2005
6 Comments
 
LVL 29

Accepted Solution

by:
sammySeltzer earned 1500 total points
ID: 39969589
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39969798
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
 
LVL 32

Expert Comment

by:awking00
ID: 39970285
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:team2005
ID: 39985316
Hi!

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

Dosent include 2013-12-20 ?
0
 
LVL 32

Expert Comment

by:awking00
ID: 39985890
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
 
LVL 2

Author Closing Comment

by:team2005
ID: 40031375
thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question