C
csgraham74
ok here goes,
i have basically inherited a database that stores date values and
totals. the total is stored as a numeric and the date values is
surprisingly stored in a STRING field - which i think is the problem.
This is all good and well whilst working in access itself but im
querying using dates in the WHERE field from asp.net and im having
problems converting the data to give me the search results that i
want. Another issue that i have is that im using the the UK date
format - have a look at the example below - all idea appreciated.
The best way to show my problem is the following
Eg. i use the following SQL
SELECT Total,orderdate FROM orders WHERE cdate(orderdate) >=
#3/1/2008# AND cdate(orderdate) <= #3/31/2008# Order by
cdate(orderdate) ASC
and it returns results such as
14/03/2008
14/03/2008
03/05/2008
03/06/2008
i would have thought that it should only return 14/03/2008 which is
what i want ?? but the other returned values are skewing my
calculation. again im based in the UK and this may be a part of my
problem. Unfortunately i cant change the string DATE field to a Date
datatype.
any help appreciated.
CG
i have basically inherited a database that stores date values and
totals. the total is stored as a numeric and the date values is
surprisingly stored in a STRING field - which i think is the problem.
This is all good and well whilst working in access itself but im
querying using dates in the WHERE field from asp.net and im having
problems converting the data to give me the search results that i
want. Another issue that i have is that im using the the UK date
format - have a look at the example below - all idea appreciated.
The best way to show my problem is the following
Eg. i use the following SQL
SELECT Total,orderdate FROM orders WHERE cdate(orderdate) >=
#3/1/2008# AND cdate(orderdate) <= #3/31/2008# Order by
cdate(orderdate) ASC
and it returns results such as
14/03/2008
14/03/2008
03/05/2008
03/06/2008
i would have thought that it should only return 14/03/2008 which is
what i want ?? but the other returned values are skewing my
calculation. again im based in the UK and this may be a part of my
problem. Unfortunately i cant change the string DATE field to a Date
datatype.
any help appreciated.
CG