CODEWITHSUNDEEP
sql-serverdatedatetime
Gerald Baretto
Gerald Baretto

Reputation: 399

Displaying dates values for current date in SQL Server

I am trying to filter out the values based on the date which should be within today's date. I am using the below query, however the query is not working as expected.

This is what I am using in SQL Server.

select values 
from table 
where date between DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) 
           and GETDATE()

I am expecting the date to be filtered for values between '2015-03-18 00:00:00' AND '2015-03-18 23:59:59' if I am executing the query on 18th March 2015.

Please guide.

Upvotes: 0

Views: 126

Answers (2)

A_Sk
A_Sk

Reputation: 4630

As You are Expecting value between '2015-03-18 00:00:00' AND '2015-03-18 23:59:59. so, you can simply compare only date part.

you can Use Cast()/Convert():

SELECT *
FROM Table
WHERE
    cast(date as date) =cast(getdate() as date)

It included 2015-03-18 00:00:00' AND '2015-03-18 23:59:59'

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

It's much better to use a >= and < than BETWEEN:

SELECT *
FROM Table
WHERE
    date >= CAST(CAST(GETDATE() AS DATE) AS DATETIME)
    AND date < DATEADD(DAY, 1, CAST(CAST(GETDATE() AS DATE) AS DATETIME))

CAST(CAST(GETDATE() AS DATE) AS DATETIME) will get you the current date without the time part that is 2015-03-18 00:00:00

DATEADD(DAY, 1, CAST(CAST(GETDATE() AS DATE) AS DATETIME)) will get you the next day, again without the time part.

So in turn, your WHERE condition would be:

WHERE
    date >= '2015-03-18 00:00:00'
    AND date < '2015-03-19 00:00:00'

Alternatively, you can use this:

SELECT *
FROM Table
WHERE
    date >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)        -- beginning of this day 
    AND date < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0) -- beginning of next day

For more date calculations, refer to this article.

Upvotes: 1

Related Questions