SQL Where Date and Time

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

try this....

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

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'.

MeowserM,

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?

Patrick

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.

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

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

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.

Thanks again.