How to pass Date to an SQL statement
I have an edit text field in a form which is bounded to a date type database field using dbDataSource.
I want to pass this value to a SQL string to search for records from Table ORDR filed name DocDuedate.
my first edit box collects the "from" value which is as below
my edittet.string = "06/03/08"
I have another text box which collects the "to" value for search
When I pass these values to SQL the executs, but does not return any records eventhough records do exist.
Can some one help with this problem(Im using SQL 2000)
Andrew Flynn replied
SBO dates are stored as YYYY-MM-DD in SQL so comparing to DD/MM/YY values will return no matches. Try getting the date value from the datasource rather than the screen item e.g. datasource.GetValue(field_id,0). This retrieves the value in YYYYMMDD format I think. You can then use something like the following to return records:
"SELECT whatever FROM ORDR WHERE DocDueDate >= '" & datasource.GetValue(fromfield_id,0) & "' AND DocDueDate <= '" & datasource.GetValue(tofield_id,0) & "'"
Note there are single quotation marks around the date values to treat them as strings.
Hope this is of some use.