SQL Where Date and Time

Posted on 2011-10-10
Last Modified: 2012-05-12

I am working in a SQL database with a "OrderDate" field with the format 'YYYY-MM-DD 00:00:00'.

I am trying to write a SQL statement to get the records where the Order date is >= '2011-10-01'.

I am getting 0 records because I am not including the time but I have tried to include 00:00:00 and it hasn't worked.

What is the proper syntax to use for
WHERE OrderDate >= '2011-10-01'

Thank you
Question by:MeowserM
    LVL 22

    Accepted Solution

    If the column you are constraining by is a DateTime column, then your format is correct.  WHERE YourDateColumn >= '2011-10-01' should provide all rows where the datetime in column YourDateColumn is greater than or equal to '2011-10-01 00:00:00.000'.

    You might want to try the following query to determine what the data in the table looks like:
    SELECT TOP 500 *
    FROM yourtable
    ORDER BY YourDatetimeColumn DESC

    Open in new window

    Note: Please substitute the column and table name appropriately for your database.
    LVL 7

    Expert Comment

    Unfortunately SQL Server doesn't have a nice TRUNC command like Oracle to truncate dates to exclude the time portion.

    Here are some examples for SQL Server

    -- Month first  
    SELECT CONVERT(varchar(12),GETDATE(), 101)  -- 06/29/2009  
    SELECT CONVERT(varchar(12),GETDATE(), 110)  -- 06-29-2009  
    SELECT CONVERT(varchar(12),GETDATE(), 100)  -- Jun 29 2009  
    SELECT CONVERT(varchar(12),GETDATE(), 107)  -- Jun 29, 2009  

    -- Year first  
    SELECT CONVERT(varchar(12),GETDATE(), 102)  -- 2009.06.29  
    SELECT CONVERT(varchar(12),GETDATE(), 111)  -- 2009/06/29  
    SELECT CONVERT(varchar(12),GETDATE(), 112)  -- 20090629  

    -- Day first  
    SELECT CONVERT(varchar(12),GETDATE(), 103)  -- 29/06/2009  
    SELECT CONVERT(varchar(12),GETDATE(), 105)  -- 29-06-2009  
    SELECT CONVERT(varchar(12),GETDATE(), 104)  -- 29.06.2009  
    SELECT CONVERT(varchar(12),GETDATE(), 106)  -- 29 Jun 2009  

    -- Time only  
    SELECT CONVERT(varchar(12),GETDATE(), 108)  -- 07:26:16  
    SELECT CONVERT(varchar(12),GETDATE(), 114)  -- 07:27:11:203  
    -- Date Only No Time (SQL 2008)
    SELECT Cast(GetDate() AS date);  -- 08/12/2011
    LVL 14

    Expert Comment

    by:nishant joshi
    try this....

    DATEDIFF(dd,orderdate,'2011-10-01') > 0
    LVL 22

    Expert Comment

    The fact that SQL doesn't have "a nice TRUNC command like Oracle" has no bearing on whether the WHERE clause should work.  SQL Server treats '2011-10-10' as though it were '2011-10-10 00:00:00.000'.  Therefore, any and all dates after '2011-10-10 00:00:00.000' should be retuirned if the query uses WHERE somedatecolumn > '2011-10-10'.
    LVL 92

    Expert Comment

    by:Patrick Matthews

    I agree with 8080_Diver: your query ought to have worked, so something else is afoot.

    1) Please double check the data type used for that column, and make sure it is [small]datetime

    2) Are you absolutely positive that you actually have data for that range?

    LVL 32

    Expert Comment

    Your syntax is correct.  The data should be returned, assuming there is data with a value greater than Oct 1, 2011.  The fact that data is not being returned seems to indicate that the data in this field is not what you expect.  So, let's examine the data.

    1)  What is the datatype of the field?

    2)  You note that the Orderdate field has "... the format 'YYYY-MM-DD 00:00:00'."  S!QL Server does not have a date format for storage per se, so this is a bit confusing.  I am assuming that this is the format returned when you simply run a query, e.g. "SELECT TOP 5 OrderDate FROM MyTable"

    3)  Try this query:  SELECT TOP 5 * FROM <your table name here> WHERE Cast(OrderDate as datetime) > Cast('20111001' as datetime)
         There is some extraneous information in this query (i.e. CASTs should not need to be explicit).  Nonetheless, does this return any records or error message?
    LVL 22

    Expert Comment

    If the author will try the query I provided, it should provide the answer to the question regarding whether or not there is data for the date range in question.

    Author Comment

    I apologize for my neglect of this question.  I got pulled off what I was doing.

    I went back in and used 8080_diver's query.  when I did a select on this table with the where clause it worked.  So it wasn't my syntax and I did actually have data in that range.

    Then I went back to my original query and I realized that I was doing a inner join on several tables and 2 of the tables had the same name for the orderdate field.  

    Could that be the problem?  I am using d.orderdate >= '2011-10-01'

    Thank you all for your comments - much appreciated
    LVL 92

    Assisted Solution

    by:Patrick Matthews
    It's OK if multiple tables are using the same column name(s) as long as you qualify your column references, which you seem to be doing with "d.orderdate".

    Now, if you are running inner joins to other tables, that is probably why you are not getting any results: with an inner join, you only return rows where the join conditions are satisfied on both tables.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>Could that be the problem?  <<
    Have you considered posting your query here, so that we don't have to guess any more?

    Author Comment

    I have 4 tables joined and I removed the tables 1 by 1 from the query and it worked when I removed the 2nd table.  The join from that table did in fact result in 0 records for the record set > 2011-10-01.

    Thank you all for helping me find this issue.


    Author Closing Comment

    Thanks again.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    732 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

    18 Experts available now in Live!

    Get 1:1 Help Now