date problem

[deep raj]

select item_id,item_name,cus_name,total_cost from purchase where date between ('2/01/2010') and ('10/01/2010')
not giving correct records while i write date ('12/12/2009') and ('19/12/2009')
it gives correct answer

i donot understand the reason for this
i write this query inside jsp page for execute sql query

[Sebastian Janisch]

i think this ('2/01/2010') should be ('02/01/2010')

[deep raj]

i tried this already but it doesnot show correct records.

[Sebastian Janisch]

In that case the format is probably wrong. I always used '01.02.2009' instead of the slashes.

[deep raj]

if we write between '01/01/2010' and '09/01/2010'
then
it shows records of day 1 to 9 even if they are from any month
while i specify only month january

[binu narayanan]

Check in what format it is stored in your database

[deep raj]

format is same in database
dd/mm/yy

[Scott Selikoff]

1) Don't use BETWEEN syntax. It's not great to start with and you have more control with >=, >, <, <=.
2) Use a PreparedStatement so that you never have to worry about date formatting.

[deep raj]

it shows records but
problem is that it shows records between two DAYS while
i want records between two DATES