What wrong with my where statement for looking for dates?

[redeemasp]

Here's my statement:

WHERE (AMGR_Opportunity.Create_Date BETWEEN CONVERT(datetime, '01/11/2004') AND CONVERT(datetime, '11/01/2005')

Lee

 

[Katy44]

Why do you have the convert in there? Why not just leave the dates?

 

[donutman]

Closing ).

 

[redeemasp]

This works now

WHERE (ORC_get_O28CreatedDate.DateCol > convert(datetime,'01-11-2004', 103)) AND (ORC_get_O28CreatedDate.DateCol < convert(datetime,'11-01-2005',103))

Problem gives back different results. :/

 

[pkailas]

what's not working?

 

[bhuninghake]

Why not simply ::

where ORC_get_O28CreatedDate.DateCol >= '2005-01-13' and ORC_get_O28CreatedDate.DateCol < '2005-01-14'

- or -

where ORC_get_O28CreatedDate.DateCol between '2005-01-13' and '2005-01-14'

When using the BETWEEN verb all records that are between or equal to the dates specified are returned.

Hope this helps.

 

[bhuninghake]

Another way to return the records for a particular date is to use the DATEPART function. With the DATEPART function you can build a WHERE statement that breaks apart each piece (year, month, day) and verifies that each piece is equal to the year, month and day you are looking for. Below, is an example that returns all the records that have a date in '2005-01-13'.

where
 DatePart(year,ORC_get_O28CreatedDate.DateCol) = '2005' and
 DatePart(month,ORC_get_O28CreatedDate.DateCol) = '01' and
 DatePart(day,ORC_get_O28CreatedDate.DateCol) = '13'

-or-

Use the FLOOR and CAST function to truncate the time portion from a date. The inner CAST function converts a DATETIME variable into a decimal value, then the FLOOR function rounds it down to the nearest integer value, and then the outer CAST function does the final conversion of the integer value back to a DATETIME value.

WHERE CAST(FLOOR(CAST(OrderDate AS FLOAT))AS DATETIME) = '2005-01-13'

 

[bhuninghake]

To use convert you would :

WHERE CONVERT(CHAR(10),ORC_get_O28CreatedDate.DateCol,121) = '2005-01-13'

 

[SQLBill]

What results are you expecting that you aren't getting?

Remember, you aren't doing >= and <= so you won't get anything from 1/11/2004 or 11/01/2005.

One problem you might be having is in the Date format. Are those dates dmy or mdy? (I know they are dmy, but SQL Server doesn't.)

Use ymd as others have suggested or add SET DATEFORMAT DMY to the beginning of the script.

-SQLBill

Posting advice: FAQ481-4875

 

[ESquared]

>= and < is the least expensive way to write the query. There are other ways that are conceptually easier to understand, but they require running a function on every row in the table rather than just comparing or even using an index.

If there is any way at all that you can go with this format, I recommend it.