search value from table by date

[neelmukhopadhya]

hi,All thanks in advance. i need a help.
i am working with a sql server 2005 database. where a table contains data like INTNO, name, dateofpurchase , item
now i want to search the value based on name and dateofpurchase fields. but not value is retrieving. i don't know what is happening.

here is the code i am using.

<code>
select INTNO from Point_item_master where PointName='ABC' and Date='1/12/2010'
</code>

PLEASE HELP

NEEL

[guillermobytes]

hi,
how do you store your dates in mysql : as a string or as a date?
what does it return if you make the same query without date in the where clause?

[neelmukhopadhya]

i am using MS sql server 2005. and i am storing as a date.
and if i make query without date it is running fine.
but when i am using date it is not returning any value.

Regards
Neel

[Rabbit]

Well, first, you used the field name Date in your SQL statement but your post says the field is named dateofpurchase.

[neelmukhopadhya]

datatype of the field is Datetime. and the column name is purchasedate as an example, You can take this as Date as the coulmn name.

[guillermobytes]

have you tried with your date formatted as '2010-01-12'

[neelmukhopadhya]

yes , i have used it.
but no result

[Rabbit]

Have you tried using CONVERT() to make it a date time?

[neelmukhopadhya]

yes i have used it, but same result. Can you give me an example

[Rabbit]

WHERE dateField = CONVERT(datetime, '1/1/2009')

[guillermobytes]

what is the output of the query :

select * from Point_item_master where PointName='ABC'

?

how is the date formatted?

try copying the date result form the query i show you, then take the date column name and try creating a new query with the date column name and date result in the where clause and see what happens.

[neelmukhopadhya]

DATE IS FORMATTED AS 11/01/2011 12:00:00 AM IN THE DATABASE.
I think sql server stores datetime in that format.i have also tried with the same format as of you . i am not getting the result.

[neelmukhopadhya]

i am getting the as result by executing the query as
select * from Point_item_master where PointName='ABC'
but whenever i am using the result i am not getting any result.

below is the line which i got from querying with pointname.
first column is the interger field. 2nd column is the datetime field. 3rd column is the string field. next column is the interger field.

1 2011-01-11 00:00:00.000 NEPAKULI 1129