CODEWITHSUNDEEP
sqlvbadatevariables
jsnarski
jsnarski

Reputation: 23

VBA variable in SQL date query

I'm trying to query the SQL database for all lines where the date is after a date given through user input. I've run into a variety of errors from "incorrect syntax near" when I surround my date with "#" to "arithmetic overflow error converting expression to". My current code looks like this:

inputdate = InputBox("Please enter the starting date (mm/dd/yyyy)")
Debug.Print inputdate

querydate = "(DTG > " & Format(inputdate, "MMDDYYYY") & ")"

select StationID, DTG, CeilingFeet from SurfaceOb where " & querydate & " and StationID = 'NZWD'"

DTG is the column name for the date-time group in the SQL database. Any idea where I am going wrong? I've tried every solution I could find over the past few days without luck. Thank you in advance.

Upvotes: 2

Views: 12018

Answers (3)

Don Jewett
Don Jewett

Reputation: 1977

Dates for SQL Server should be formatted as a date or date/time, qualified with single-quotes:

Date in ISO unseparated date format 

querydate = "(DTG > '" & Format(inputdate, "yyyymmdd") & "')"

Date/Time in ISO 8601 format 

querydate = "(DTG > '" & Format(inputdate, "yyyy-mm-ddThh:mm:ss.000") & "')"

Upvotes: 1

Brett Wolfington
Brett Wolfington

Reputation: 6627

The primary issue is that dates must be enclosed in single-quotes. Here is a complete working example (minus a valid connection string) that should explain how to achieve your objective. Note that you will also want to switch to the ISO date format, in which the order is Year-Month-Day (YYYY-MM-DD).

Sub UpdateRecords()
    Dim connection As New ADODB.connection
    Dim recordset As New ADODB.recordset
    Dim connectionString As String
    Dim query As String
    Dim inputdate As Date
    Dim querydate As String

    inputdate = InputBox("Please enter the starting date (mm/dd/yyyy)")
    querydate = "(DTG > '" & Format(inputdate, "yyyy-mm-dd") & "')"
    query = "select StationID, DTG, CeilingFeet from SurfaceOb where " & querydate & " and StationID = 'NZWD'"

    connectionString = "..."        
    connection.Open connectionString
    recordset.Open query, connection
    ActiveSheet.Range("A1").CopyFromRecordset recordset
End Sub

Upvotes: 1

James Groves
James Groves

Reputation: 71

Date format for MySQL is YYYY-MM-DD. Also, as DTG is datetime, you'll need DATE(DTG) > DATE(NOW()) for example - DATE() is a MySQL function that checks only the date portion of a datetime stamp.

Your query should look like this:

querydate = "(DATE(DTG) > " & Format(userinput, "YYYY-MM-DD") & ")"

select StationID, DTG, CeilingFeet from SurfaceOb where " & querydate & " and StationID = 'NZWD'"

Upvotes: 0

Related Questions