SQL Query using two dates

Question

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).

Answer 1

where date > '10-MAY-20' And date < '13-MAY-20'

'10-MAY-20' is not a date, it is a string. A date doesn't have any format, based on your locale-specific NLS settings it is displayed in a human readable format using TO_CHAR and proper format mask.

Also, you should avoid using two digit YY representation for year, it's the whole reason Y2K bug started. Always use YYYY format.

To convert a string into date:

Use TO_DATE and appropriate format mask:

where  date_col > TO_DATE('10-MAY-2020', 'DD-MON-YYYY')  
and    date_col < TO_DATE('13-MAY-2020', 'DD-MON-YYYY')

Or, use ANSI date literal which uses fixed format 'YYYY-MM-DD':

where  date_col > DATE '2020-05-10' and date < DATE '2020-05-13'

Another thing, DATE is an Oracle reserved word, you can see the documentation. Perhaps, you are using quoted identifier "date" i.e. using double-quotation marks around the reserved word.

i dont understand why is wasnt working with '10-MAY-20' as this is the required format in Oracle SQL developer

That's probably because your NLS settings of SQL Developer is set to 'DD-MON-YY', so when you pass the date in that format it doesn an implicit conversion to date. However, if you change the format it won't work:

alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD';

Answer 2

A date is not a string, and if you deal with strings that represent a date, it's up to the driver or to the database to interpret this as a date. Oracle expect a TO_DATE-function, for SQL server you can write it without function - if you use the right format. For other databases it might be different.

I always advocate for using ADODB.Parameter to pass parameters. This means slightly more code, but no more hassle with parameter formatting (true also for strings, floating numbers etc) and protection against SQL injection. You need to create an ADODB.Command object plus one ADODB.Parameter per parameter that you pass to the database. In the query, you replace the parameter value simply by a ?

dim d1 as Date, d2 as Date    
d1 = CDate("2020-05-10")   ' Control of the date is completely in your hand.
d2 = CDate("2020-05-13")

StrQuery = "select  ref, date from records  where  date > ? And date < ?"
' Create command
Dim cmd as New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandText = StrQuery 

' Create 2 parameter of type Date and pass the date values
Dim param as ADODB.Parameter
Set param = cmd.CreateParameter("p1", adDate, adParamInput, , d1)
cmd.Parameters.Append param
Set param = cmd.CreateParameter("p2", adDate, adParamInput, , d2)
cmd.Parameters.Append param

Set rst = cmd.Execute