• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

SQL Dates


I have a field in my DB that is a SmallDateTime, I am trying to get all the records that are for today, I tried using:

SELECT id, DeliveryDate FROM tbl_items WHERE (DeliveryDate = GetDate())

But this does not work it returns nothing

How can I specify the date correctly, do I need to cast the DeliveryDate to a Date not time as well?

2 Solutions
Try this:

SELECT id, DeliveryDate
FROM tbl_items
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>a field in my DB that is a SmallDateTime
This has a time component

>= GetDate())
This also has a time component

>I am trying to get all the records that are for today,
This is date without time, so if you want to get the date only you'll have to compare both values as date:

SELECT id, DeliveryDate FROM tbl_items WHERE CAST(DeliveryDate as date) = CAST(GetDate() as date)
It wont work like that because GETDATE gets the current datetime (including hours, minutes, seconds etc) and you only want the day, try something like this:

SELECT id, DeliveryDate FROM tbl_items WHERE (YEAR(DeliveryDate ) = YEAR(GETDATE()) AND MONTH(DeliveryDate ) = MONTH(GETDATE()) AND DAY(DeliveryDate ) = DAY(GETDATE()))
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Éric MoreauSenior .Net ConsultantCommented:
SELECT id, DeliveryDate FROM tbl_items WHERE (DeliveryDate = convert(varchar(10), GETDATE(), 120))
Berkson WeinTech FreelancerCommented:
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.

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.
Saurabh BhadauriaCommented:
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)
Scott PletcherSenior DBACommented:
FROM dbo.tbl_items
    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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Alpesh PatelAssistant ConsultantCommented:
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.
sanjshah12Author Commented:
Wow so many anwers, thank guys!

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now