[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

SQL Where Date and Time


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
  • 3
  • 3
  • 2
  • +4
2 Solutions
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:
FROM yourtable
ORDER BY YourDatetimeColumn DESC

Open in new window

Note: Please substitute the column and table name appropriately for your database.
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
nishant joshiTechnology Development ConsultantCommented:
try this....

DATEDIFF(dd,orderdate,'2011-10-01') > 0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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'.
Patrick MatthewsCommented:

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?

Brendt HessSenior DBACommented:
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?
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.
MeowserMAuthor Commented:
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
Patrick MatthewsCommented:
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.
Anthony PerkinsCommented:
>>Could that be the problem?  <<
Have you considered posting your query here, so that we don't have to guess any more?
MeowserMAuthor Commented:
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.

MeowserMAuthor Commented:
Thanks again.

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now