FormatDateTime in SQL queries RRS feed

  • Question

  • I use VB2010 Express with MS Access (XP) tables in United Kingdom and I am building the SQL query below to pass to an adapter.fill command.

    adapter.SelectCommand= = NewOleDb.OleDbCommand("SELECT * FROM receipts WHERE [when] >= #"& span& "#")

    The dates (when) in the receipts table are formatted to ShortDate time in the dd/MM/yyyy format as is the norm in UK.  If I use the formula when = CStr(FormatDateTime(DateAdd(DateInterval.Month, -6, Now), DateFormat.ShortDate)) I get a date in UK format but the subsequent SQL query gives the wrong number of "picks" from the database as it expects the date in MM/dd/yyyy format and I have to use the formula when = CStr(Format(DateAdd(DateInterval.Month, -6, Now), "MM/dd/yyyy")) to get the right answer.

    My question - is there a more elegant and intuitive way to do this?


    Colin Reid

    Saturday, July 7, 2012 4:15 PM


  • Yes, there is. FormatDateTime is as outdated as DateAdd as concatenating the SQL string yourself. Use this instead:

          Dim cmd As New OleDb.OleDbCommand("SELECT * FROM receipts WHERE [when] >= ?")
          cmd.Parameters.AddWithValue("@datum", Date.Now.AddMonths(-6))

    Now need to care about date formats anymore.

    Be aware that "Now" also includes the time of the day, so if you want the day only, use Date.Today.


    Saturday, July 7, 2012 4:39 PM