Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

On applying the where condition :

WHERE DateFilled BETWEEN '02/01/2014' AND '02/28/2014'

My syntax is not detecting the values which are filled on 02/28/2014.

If I write the syntax as

WHERE DateFilled >= '02/01/2014'  

I am getting the output.

In the SQL Server table, DateFilled column has a value as follows.

2014-02-28 09:24:00.000

What could be the reason? Any date conversion is required. How to get StartDate and EndDate using BETWEEN condition?

share|improve this question
    
If you have other code that uses BETWEEN on columns that have time components (hint: don't do that), I would strongly suggest going back and checking to make sure it's doing what you expect. –  Jon Seigel Mar 8 at 20:52

2 Answers 2

up vote 6 down vote accepted

I am assuming that DateFilled has a data type of DateTime or other date type which includes a time portion. When using DateTime values in T-SQL be aware that a time is always included and if not specified midnight is used by default. See: http://technet.microsoft.com/en-us/library/ms180878(v=sql.105).aspx#ConversionBetweenStringLiteralsandDateandTimeDataTypes When the input string is "DATE only" then "the The TIME part defaults to 00:00:00." In your case the string "02/28/2014" is converted to a datetime value of 2014-02-28 00:00:00.000.

Your query is interpreted as DateFilled between '02/01/2014 00:00:00 .000' and '02/28/2014 00:00:00.000' thus the record with a datetime of 2014-02-28 09:24:00.000 (which occurs after midnight on, is not found.

Your query may be re-written as

SELECT Column1, Column2
FROM MyTable
WHERE DateFilled >= '02/01/2014'
   AND DateFilled < '03/01/2014';
share|improve this answer

Try:

WHERE CAST(DateFilled AS DATE) BETWEEN '02/01/2014' AND '02/28/2014'

This casts the DATETIME variable into a DATE variable which does not contain time. This does not work in SQL Server 2000 or 2005 though. The DATE datatype was introduced in SQL Server 2008.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.