SQL Dates

Try this:

SELECT id, DeliveryDate
FROM tbl_items
WHERE DeliveryDate = DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)

SELECT id, DeliveryDate FROM tbl_items WHERE (DeliveryDate = convert(varchar(10), GETDATE(), 120))

Are you storing the time of day as well, or just the date?

As stated above, the query will return nothing because GETDATE() has a time component that is not going to match the time component in your field.
If you would like to ignore the time component you can try the code I posted.

jimhorn's solution would work in SQL Server 2008+, but if you're using SQL 2k5 you're going to have to deal with the time component of your field and GETDATE.

Hi,

If your dateformat is mm/dd/yyyy then you can write the following query

SELECT id, DeliveryDate FROM tbl_items WHERE (DeliveryDate = CONVERT(varchar, GetDATE(), 101))

and if the date format is yyyy/mm/dd then instead of 101 write 111.

Hope this helps you.

Try this..


SELECT id, DeliveryDate FROM tbl_items WHERE
DeliveryDate between
cast(convert(char(10),getdate(),121) as datetime)  and
 cast( (convert(char(10),getdate(),121) + ' 23:59:59:000') as datetime)

SELECT
    ...
FROM dbo.tbl_items
WHERE
    DeliveryDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
    DeliveryDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)


Which is equivalent to >= today at midnight and < tomorrow at midnight, that is, ALL of TODAY only.

That makes time irrelevant, which is best, rather than trying to code an exact ending time, since it's so open to mistakes (for example, ' 23:59:59:000' is not correct, it should be ' 23:59' since the column was stated as smalldatetime; for full datetime, it would be '23:59:59.997', not 998 or 999 -- no way everyone will remember all that!!).

And if the time were later changed to full datetime, or datetime2, any code with explicit times would again be wrong anyway.

please read this article to understand the issue, and solve it:
https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

DateTime have date and time part, however getdate contains current date and time. so I will not get  todays data. for that you have to write code for datestart and end with time.

Wow so many anwers, thank guys!