Check date between two dates in T-SQL

Question

I have a stored procedure where want check that a date is between a fixed date and the current date/time (with GETDATE()):

SELECT
    a, b
FROM myTbl
WHERE
    DATE BETWEEN 'Day start datetime' AND GETDATE()

...for example :

WHERE
    DATE BETWEEN '2013-09-10 00:00:00.00' AND 'GETDATE()'

How to do it?

Answer 1

A pair of DATEADD/DATEDIFF calls will round a date down to the previous midnight:

SELECT a , b
FROM myTbl
WHERE DATE BETWEEN DATEADD(day,DATEDIFF(day,0,GETDATE()),0) and GETDATE()

Alternatively, if you're on SQL Server 2008 or later:

SELECT a , b
FROM myTbl
WHERE DATE BETWEEN CONVERT(date,GETDATE()) and GETDATE()

Answer 2

'GETDATE()' is a string literal, GETDATE() is a T-SQL function.

Your query should look like:

SELECT a , b
FROM myTbl
WHERE DATE BETWEEN '2013-09-10 00:00:00.0' and GETDATE()

Answer 3

I think WHERE DATE BETWEEN '2013-09-10 00:00:00.00' and GETDATE() (without the single quotes around the GETDATE() call) should work just fine.