Check date between two dates in T-SQL

sql sql-server tsql between
GeoVIP picture GeoVIP · Sep 10, 2013 · Viewed 81.1k times · Source

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

Damien_The_Unbeliever picture Damien_The_Unbeliever · Sep 10, 2013

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()

Related questions

SQL : BETWEEN vs <= and >=

In SQL Server 2000 and 2005: what is the difference between these two WHERE clauses? which one I should use on which scenarios? Query 1: SELECT EventId, EventName FROM EventMaster WHERE EventDate BETWEEN '10/15/2009' AND '10/18/2009' Query 2: SELECT EventId, EventName FROM …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More