i have a datatime field which has both date and time value now i want to get the records on the basis of date only... i have tried query "select dd,aa from table where datetime=#11/03/2004#" but it is not working..

It depends on what kind of database you're using...It looks like either access or sql server. You probably need the DatePart function. Good luck, -SN

You can still just use the date (if it is actually a datetime type) So... SELECT dd,aa FROM TABLE WHERE DateTime='11/03/2004' is the same as SELECT dd,aa FROM TABLE WHERE DATEPART(day,DateTime)=3 AND DATEPART(month,DateTime)=11 AND DATEPART(year,DateTime)=2004 If, however, the field is not of a datetime type, but a char or varchar, then you will need to either cast it as a datetime or use a wildcard: CAST EXAMPLE: SELECT dd,aa FROM TABLE WHERE CAST(DateTime AS DATETIME)='11/03/2004' WILDCARD EXAMPLE: SELECT dd,aa FROM TALBE WHERE DateTime LIKE '11/03/2004%' Hope this helps Chi "They mostly come at night...mostly"

right! i am using access and the database field has the date and time both values in it now suppose i want to view the records of 11/2/2004 there are records in database but by calling the query SELECT Logs.dnumber,Logs.duration, Logs.consumed_units, Logs.cardserial, Logs.callcharges, Logs.datetime FROM Logs WHERE ((Logs.datetime)='11/2/2004') it gave me type mismatch error..... and if i remove the '' from the date it gave no error but gives that no record found.

The type mismatch error your getting is because you're trying to convert a string to a date. Use # instead of ', like you did in your first post. I just tried it in access, Chi's first method only returned the rows that have the time portion set to 12:00 AM. It may work in SQL server...I'll have to try it. To get the result I wanted, I had use Chi's second query (using the equivalent day, year, month functions): SELECT * from payments where Year(payment_date)=Year(#1/6/04#) and Month(payment_date)=Month(#1/6/04#) and Day(payment_date)=Day(#1/6/04#) Shame on MS for not having a function to extract the entire date...I thought datepart would be able to do it in one call. -SN

Yeah well access is a lot more weird than SQL server. SELECT Table1.ID, Table1.mydate FROM Table1 WHERE (((Table1.mydate)=#1/6/2004#)); works as long as the data is 1/6/2004 it fails on a data that has time in it as well. ACCESS SUCKS Here is my sample info: Table Name is Table1 Field1 is ID (autoid primary key) Field2 is MyDate (datetime) DATA: FIELD1 FIELD2 1 11/10/2004 10:00:00 AM 2 1/6/2004 3 1/6/2004 10:00:00 PM the query above returns this: FIELD1 FIELD2 2 1/6/2004 but it should return this: FIELD1 FIELD2 2 1/6/2004 3 1/6/2004 10:00:00 PM this access query return the correct expected data: SELECT Table1.ID, Table1.mydate FROM Table1 WHERE DATEPART('m',mydate)=1 and datepart('d',mydate)=6 and datepart('yyyy',mydate)=2004; I restate my first observation: ACCESS SUCKS. Chi "They mostly come at night...mostly"

thanks guys this helps alot.