SQL Dates

Posted on 2012-09-19
Last Modified: 2012-09-23

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?

Question by:sanjshah12
    LVL 13

    Expert Comment

    Try this:

    SELECT id, DeliveryDate
    FROM tbl_items
    LVL 65

    Accepted Solution

    >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)
    LVL 5

    Assisted Solution

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

    Expert Comment

    by:Éric Moreau
    SELECT id, DeliveryDate FROM tbl_items WHERE (DeliveryDate = convert(varchar(10), GETDATE(), 120))
    LVL 15

    Expert Comment

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

    Expert Comment

    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.

    Expert Comment


    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.
    LVL 12

    Expert Comment

    by:Saurabh Bhadauria
    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)
    LVL 68

    Expert Comment

    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.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    LVL 21

    Expert Comment

    by:Alpesh Patel
    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.

    Author Comment

    Wow so many anwers, thank guys!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now