Hello,

I write my date as string in a table ('dd/mm/yyyy').
Now I wont to do a select but it has to do with a where clause on this date.

Can I do this:
sDate = DateAdd("m","-3",date()) select data from last 3 months

"SELECT * FROM tblCV WHERE cv_date <='" & dDate & "'"

I think it works better if I change the date format in:
yyyy/mm/dd since I save this as string in the database.


What I need to do:
- select cv's between a certain date
- select cv's from last 3 months

My date is written as a string in the database.


Please an opinion or solution

Thanks



:: soulcreation.com ::

The best soultion is to change column's data type to datetime. Then, you can use SQL operator BETWEEN for the first criteria, and be sure that operators > and < compare values properly...



...but the Soon is eclipsed by the Moon

If you must use a string for the date then

YYYY/MM/DD

is the way to go as it should sort correctly if leading zeros for months and days are enforced by your application and you may be able to construct a query to with greater thans or less thans to return what you need.

sDate = DATEADD("m",-3,date())
strSQL = "SELECT * FROM tblcv WHERE CAST(cv_date as datetime) <= '" & sdate & "'"


The ASP above might create the SQL string you need if you are going against a SQL server... I couldn't test it though

When using a database that supports a date data type I would definitely look at redefining the column, especially when you want to perform date math.

Hope this helped
HR

thanks for the answers.

I cannot change the field type.
The application was initially designed and programmed by someone else.
I am hired to make changes, add fields and search possibillities etc.

I will change the string format in yyyy/mm/dd which is the best format since you can easily do a SORT and also a BETWEEN.



:: soulcreation.com ::