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

no records are output when start and end date are same


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.
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.
3 Solutions
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'
RockingAuthor Commented:
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
Dale FyeCommented:
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.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
RockingAuthor Commented:
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')
Dale FyeCommented:
Yes, that should work.
Scott PletcherSenior DBACommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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