SQL Server Date Conversion

Question

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?

Answer 1

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';

Answer 2

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.