This may be a dumb question but if my database has a field called order_date and the dates in that field are displayed as "2001/03/23" how can I order by date (im trying to filter out all but the month march)?
heres what I have so far (its not working):
SELECT O.ORDER_DATE, R.TITLE, OL.QUANTITY, OL.ORDER_PRICE
FROM ORDERS O, ORDERLINE OL, RECORDING R
WHERE O.ORDER_NUMBER = OL.ORDER_NUMBER AND OL.RECORDING_ID = R.RECORDING_ID
AND O.ORDER_DATE >= 03/01/2001 and O.ORDER_DATE < 04/01/2001
ORDER BY R.TITLE, O.ORDER_DATE
O.ORDER_NUMBER = OL.ORDER_NUMBER
AND OL.RECORDING_ID = R.RECORDING_ID
AND convert(varchar(10),O.ORDER_DATE,101) between '03/01/2001' and '03/31/2001'
The server does not know that 03/01/2001 and 04/01/2001 are supposed to be dates, and is probably interpreting them as division equations. Use this syntax instead:
O.ORDER_DATE >= '03/01/2001' and O.ORDER_DATE < '04/01/2001'
SQL Server will implicitly translate the date strings into datetime data types.
It's not necessary if the data is being stored in datetime format. The table doesn't know or care about the front-end. If the data is being stored as a string, then then convert is probably a good idea.
Select * from abc where refdate is '06/08/2000'????
convert(varchar(10),O.ORDER_DATE,101) between '03/01/2001' and '03/31/2001'
...is equivalent to:
O.ORDER_DATE between '03/01/2001' and '03/31/2001'
..when the data is in the whole-date values that Osiris specified. Otherwise:
O.ORDER_DATE >= '03/01/2001' < '04/01/2001'
...also does the job.
When you wrote: "convert(varchar(10),O.ORDER_DATE,101) brings the datetime field into the same format you are trying to compare with i.e mm/dd/yyyy" it sounds as if you are saying that a value stored as yyyy/mm/dd must be converted before it can be compared to mm/dd/yyyy. I think what you meant is that any time portion needs to be truncated or a value such as '03/31/2001 08:15:32' would be excluded.
I just wanted to make sure that Osiris1012 understands that the format in which a datetime field is displayed has nothing to do with how it is stored or how it was entered. ie that it is acceptable to compare the value 6/9/2000 to either '06/09/2000' or '2000-06-09' or '2000/06/09'.