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

x
?
Solved

no records are output when start and end date are same

Posted on 2014-03-19
7
Medium Priority
?
583 Views
Last Modified: 2014-03-19
Hi,

I have a table with three columns namely

id int
name varchar(40)
create_date datetime  -> data ex. 2014-03-15 15:00:09.923

I write the query below to get records within a particular date,everthhing works fine except when the start date and end date are same ,no result is displayed.
Query
select id,name from test where create_date >='2012/12/12' and create_date<='2012/12/12'

It return no record,however in the database record exists.
what modification need to get above result.
0
Comment
Question by:Rocking
7 Comments
 
LVL 9

Expert Comment

by:dustock
ID: 39940156
you have a datetime column but you aren't specifying a time.  Try something like what I have below.

select id,name from test where create_date >= '2012/12/12 00:0:01' and create_date <= '2012/12/12 23:59:59'
0
 

Author Comment

by:Rocking
ID: 39940166
i think there is no issue of time

how come it returns the record if there is a gap between a single day,
select id,name from test where create_date >='2012/12/12' and create_date<='2012/12/13'

above works fine
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 800 total points
ID: 39940183
Most likely , the column you are trying to filter on [Create_Date] also contains a time value.  You may not be able to see it because of the way the field is formatted, either in the table design or in a query, but if you eliminate any format property from the field it will probably display with a data and time.

Generally, when working with dates, I write my queries like:

WHERE [Create_Date] >= [StartDate]
AND [Create_Date] < [StartDate] + 1

This way, you will capture all records created starting at midnight on the [StartDate] and running up to, but not including midnight of the next night.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 800 total points
ID: 39940184
>i think there is no issue of time
You are not correct.

>where create_date >='2012/12/12'
Without a time component, as dunstock stated above, SQL will interpret it as the stroke of midnight that begins the day, i.e. '2012-12-12 00:00:00.000'

Therefore, your SQL is the same as saying..
select id,name from test where create_date >='2012-12-12 00:00:00.000' and create_date<='2012-12-12 00:00:00.000'.

'2012-12-12 00:00:00.000' will meet the above criteria, as it equals both.
'2014-12-12 15:00:09.923' does not, as it is greater than both.

SQL expert PortletPaul has an article on Beware of Between that does a great job of illistrating your situation.
0
 

Author Comment

by:Rocking
ID: 39940217
is the below query correct to get the desired result

select id,name from test where create_date >='2012/12/21' and
create_date < DATEADD(dd,1,'2012/12/21')
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 39940375
Yes, that should work.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 39940523
In general, but it's best to write it using literals, like this:

where create_date >='20121221' and
    create_date < 20121222'

Otherwise you might hurt performance and/or have unexpected side effects if you change the data type of the column, for example, to datetime2.
0

Featured Post

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.

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 …
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

886 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