<div>
Unfortunately SQL Server doesn't have a nice TRUNC command like Oracle to truncate dates to exclude the time portion.<br />
<br />
Here are some examples for SQL Server<br />
<br />
-- Month first &nbsp;<br />
SELECT CONVERT(varchar(12),GETDAT<wbr />E(), 101) &nbsp;-- 06/29/2009 &nbsp;<br />
SELECT CONVERT(varchar(12),GETDAT<wbr />E(), 110) &nbsp;-- 06-29-2009 &nbsp;<br />
SELECT CONVERT(varchar(12),GETDAT<wbr />E(), 100) &nbsp;-- Jun 29 2009 &nbsp;<br />
SELECT CONVERT(varchar(12),GETDAT<wbr />E(), 107) &nbsp;-- Jun 29, 2009 &nbsp; <br />
<br />
-- Year first &nbsp;<br />
SELECT CONVERT(varchar(12),GETDAT<wbr />E(), 102) &nbsp;-- 2009.06.29 &nbsp;<br />
SELECT CONVERT(varchar(12),GETDAT<wbr />E(), 111) &nbsp;-- 2009/06/29 &nbsp;<br />
SELECT CONVERT(varchar(12),GETDAT<wbr />E(), 112) &nbsp;-- 20090629 &nbsp;<br />
<br />
-- Day first &nbsp;<br />
SELECT CONVERT(varchar(12),GETDAT<wbr />E(), 103) &nbsp;-- 29/06/2009 &nbsp;<br />
SELECT CONVERT(varchar(12),GETDAT<wbr />E(), 105) &nbsp;-- 29-06-2009 &nbsp;<br />
SELECT CONVERT(varchar(12),GETDAT<wbr />E(), 104) &nbsp;-- 29.06.2009 &nbsp;<br />
SELECT CONVERT(varchar(12),GETDAT<wbr />E(), 106) &nbsp;-- 29 Jun 2009 &nbsp;<br />
<br />
-- Time only &nbsp;<br />
SELECT CONVERT(varchar(12),GETDAT<wbr />E(), 108) &nbsp;-- 07:26:16 &nbsp;<br />
SELECT CONVERT(varchar(12),GETDAT<wbr />E(), 114) &nbsp;-- 07:27:11:203 &nbsp;<br />
&nbsp; &nbsp;<br />
-- Date Only No Time (SQL 2008) <br />
SELECT Cast(GetDate() AS date); &nbsp;-- 08/12/2011 <br />

</div>


<div>
try this....<br />
<br />
DATEDIFF(dd,orderdate,'201<wbr />1-10-01') &gt;&nbsp;0
</div>


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


<div>
MeowserM,<br />
<br />
I agree with 8080_Diver: your query ought to have worked, so something else is afoot.<br />
<br />
1) Please double check the data type used for that column, and make sure it is [small]datetime<br />
<br />
2) Are you absolutely positive that you actually have data for that range?<br />
<br />
Patrick
</div>


<div>
Your syntax is correct. &nbsp;The data should be returned, assuming there is data with a value greater than Oct 1, 2011. &nbsp;The fact that data is not being returned seems to indicate that the data in this field is not what you expect. &nbsp;So, let's examine the data.<br />
<br />
1) &nbsp;What is the datatype of the field?<br />
<br />
2) &nbsp;You note that the Orderdate field has &quot;... the format 'YYYY-MM-DD 00:00:00'.&quot; &nbsp;S!QL Server does not have a date format for storage per se, so this is a bit confusing. &nbsp;I am assuming that this is the format returned when you simply run a query, e.g. &quot;SELECT TOP 5 OrderDate FROM MyTable&quot;<br />
<br />
3) &nbsp;Try this query: &nbsp;SELECT TOP 5 * FROM &lt;your table name here&gt; WHERE Cast(OrderDate as datetime) &gt;&nbsp;Cast('20111001' as datetime)<br />
&nbsp; &nbsp; &nbsp;There is some extraneous information in this query (i.e. CASTs should not need to be explicit). &nbsp;Nonetheless, does this return any records or error message?
</div>


<div>
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.
</div>


<div>
I apologize for my neglect of this question. &nbsp;I got pulled off what I was doing.<br />
<br />
I went back in and used 8080_diver's query. &nbsp;when I did a select on this table with the where clause it worked. &nbsp;So it wasn't my syntax and I did actually have data in that range.<br />
<br />
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. &nbsp;<br />
<br />
Could that be the problem? &nbsp;I am using d.orderdate &gt;= '2011-10-01'<br />
<br />
Thank you all for your comments - much appreciated
</div>


<div>
&gt;&gt;Could that be the problem? &nbsp;&lt;&lt;<br />
Have you considered posting your query here, so that we don't have to guess any more?
</div>


<div>
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. &nbsp;The join from that table did in fact result in 0 records for the record set &gt;&nbsp;2011-10-01.<br />
<br />
Thank you all for helping me find this issue.<br />
<br />

</div>


<div>
<div class="content wysiwyg-content">
Hello:<br />
<br />
I am working in a SQL database with a &quot;OrderDate&quot; field with the format 'YYYY-MM-DD 00:00:00'.<br />
<br />
I am trying to write a SQL statement to get the records where the Order date is &gt;= '2011-10-01'.<br />
<br />
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.<br />
<br />
What is the proper syntax to use for <br />
WHERE OrderDate &gt;= '2011-10-01'<br />
<br />
Thank you
</div>
</div>


Hello:

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

SQL Where Date and Time

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

MySQL Server

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.