Solved

Select records using between...

Posted on 2014-04-01
6
136 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 28

Accepted Solution

by:
sammySeltzer earned 500 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 69

Expert Comment

by:ScottPletcher
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 31

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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 31

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now