Friday, February 10, 2012

Convert Date in SQL - Query

Hi

im using access 2000 with ASP.

i do have the following query

SQL = "SELECT * FROM tblKundenstamm, tblVertrag " _
& "WHERE tblVertrag.strKSID = tblKundenstamm.IDRef AND strDate BETWEEN " & CDate(firstDate) & " AND " & CDate(lastDate) & " " _
& "ORDER BY tblKundenstamm.strFirma, tblVertrag.strVertragNr"

strDate is not a Date/Time Field, it is a text field.

How do i convert strDate into a Date within this query?

thanksHi,

To convert strDate into a Date within this query : (first the conversion is done in date and then for checking with firstdate and lastdate all the three dates are converted back to character in ddmmyyyy format. The format can be anything, but all should have the same format.)

SQL = "SELECT * FROM tblKundenstamm, tblVertrag " _
& "WHERE tblVertrag.strKSID = tblKundenstamm.IDRef AND
to_char(to_date(strDate,'ddmmyyyy'),'ddmmyyyy')
BETWEEN " & to_char(firstDate,'ddmmyyyy') & " AND " & to_char(lastDate,'ddmmyyyy') & " " _
& "ORDER BY tblKundenstamm.strFirma, tblVertrag.strVertragNr"

Thanks ...
Bye

No comments:

Post a Comment