I can run the below SQL query when it contains a single date. When I introduce a second date no results are returned.
I also got this to work using the DD/MM/YY
format, but only with one date.
I am using Office 2010 and connecting to an Oracle SQL database.
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String
ConnectionString = SQLConnect 'This is a function that resolves to the connection string
cnn.Open ConnectionString
cnn.CommandTimeout = 180
StrQuery = "select ref, date from records where date > '10-MAY-20' And date < '13-MAY-20'"
rst.Open StrQuery, cnn
ThisWorkbook.Sheets("Sheet 1").Range("A2").CopyFromRecordset rst
rst.Close
cnn.Close
I tried amending the query to "select noteno, trandate from records where date between '10-MAY-20' And '13-MAY-20'".
Both queries work in Oracle SQL Developer when a single date is used.
The "select noteno, trandate from records where date like '%MAY-20'" also doesn't work when run via VBA (but fine in Oracle SQL developer).
10-MAY-20
and at the same time more than13-MAY-20
if you need the dates in between then switch your<
and>
signs. If you mean all the dates that are not in between then yourAnd
needs to be anOr
. • Also make sure your date has the correct format you migh need to change it to2020-05-10
(depends on your setup, check it). – PᴇʜDATE
doesn't have any format, it's the locale-specificNLS settings
which have format to display the date in string.'10-MAY-20'
is NOT a date, it is a string. You must useTO_DATE
to convert it into date. Or, use theANSI date literal
. Also, stop using the two-digit representation for year. It's the whole reason Y2K bug started. – Lalit Kumar BYYYY-MM-DD
according to ISO 8601. This date format is the only one that cannot be misunderstood in international communication (xkcd.com/1179). The formatYYYY-DD-MM
does not exist also see stackoverflow.com/questions/2254014/… – Pᴇʜ"date"
? – user9950041