Check date between two t-sql dates

Question

Hi, I have a stored procedure where a chek date is required between the start of getdate and the current date.

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

in the place where I want to write the date at the beginning of the day, 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 will round the date until the last midnight:

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

Alternatively, if you are using 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 request should look like this:

 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 call to GETDATE() ) should work fine.

Answer 4

You can get Day start date time by converting the return value of Getdate () to Date and again to Datetime, as shown below.

 select a,b from myTbl where [date] between convert(datetime,convert([date], getdate())) and getdate()