no records are output when start and end date are same

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.
RockingAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
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
 
dustockCommented:
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
 
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
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
Jim HornConnect With a Mentor Microsoft 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.
0
 
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')
0
 
Dale FyeCommented:
Yes, that should work.
0
 
Scott PletcherConnect With a Mentor Senior 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.
0
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.

All Courses

From novice to tech pro — start learning today.