Invalid Date on SQL & ADO
Giovani Batoni got an Invalid Date error in a Visual Basic ADO query comparing rpt_dt to a date string, even with a hard-coded date. Rick Cardarelle and Siva advised US MM/DD/YYYY format, with # delimiters for Access and single quotes for SQL databases. Manfred Herr suggested the ODBC escape format {d yyyy/mm/dd}. No confirmation of a fix.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Dim sql as string sql = "select rpt_dt, cbs_id from cbs where rpt_dt = ' " & Date & "'" cn.Open sql .... I get an error: Invalid Date Even if I hard code the date still gives me the error msg. What am I doing wrong?
What country are you in? I think the date needs to be in US (MM/DD/YYYY) format... I know that is the case with Jet and Access. Also if the field is a date field you need to enclose it in the pound sign (#) not the single quote ('). -- Richard Cardarelle, MCP Eclipse Applications www.eclipseapplications.com Giovani Batoni <gbatoni@mediaone.net> wrote in message news:37D16382.5B68B7C6@mediaone.net... Dim sql as string sql = "select rpt_dt, cbs_id from cbs where rpt_dt = ' " & Date & "'" cn.Open sql .... I get an error: Invalid Date Even if I hard code the date still gives me the error msg. What am I doing wrong?
You have to check two things, 1. The date format has to be is US format MM/DD/YYYY 2. If you use Access DB then enclose date with '#' or if you use SQL DB then use it as single cote ' Giovani Batoni <gbatoni@mediaone.net> wrote in message news:37D16382.5B68B7C6@mediaone.net... > > Dim sql as string > > sql = "select rpt_dt, cbs_id from cbs where rpt_dt = ' " & Date & "'" > cn.Open sql .... > > I get an error: Invalid Date > > Even if I hard code the date still gives me the error msg. > > What am I doing wrong? > > > >
Hallo, >Dim sql as string >sql = "select rpt_dt, cbs_id from cbs where rpt_dt = ' " & Date & "'" >cn.Open sql .... >I get an error: Invalid Date >Even if I hard code the date still gives me the error msg. >What am I doing wrong? better use the SQL format for dates if you prepare these string. {d "yyyy/mm/dd"}. Tschau Manfred