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

The date cannot be less than 10-MAY-20 and at the same time more than 13-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 your And needs to be an Or. • Also make sure your date has the correct format you migh need to change it to 2020-05-10 (depends on your setup, check it). — Pᴇʜ
– Pᴇʜ, Commented May 19, 2020 at 7:10
DATE doesn't have any format, it's the locale-specific NLS settings which have format to display the date in string. '10-MAY-20' is NOT a date, it is a string. You must use TO_DATE to convert it into date. Or, use the ANSI date literal. Also, stop using the two-digit representation for year. It's the whole reason Y2K bug started. — Lalit Kumar B
– Lalit Kumar B, Commented May 19, 2020 at 7:26
@StewartLancaster there only exists YYYY-MM-DD according to ISO 8601. This date format is the only one that cannot be misunderstood in international communication (xkcd.com/1179). The format YYYY-DD-MM does not exist also see stackoverflow.com/questions/2254014/… — Pᴇʜ
– Pᴇʜ, Commented May 19, 2020 at 7:32
@StewartLancaster checkout Lalit's answer below too. He explained some more. — Pᴇʜ
– Pᴇʜ, Commented May 19, 2020 at 7:40

Lalit Kumar B · Accepted Answer · 2020-05-19 07:39:56Z

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';

I would say a general and big recommendation if you work with computers is: Make sure the only date format you ever use is YYYY-MM-DD as this is the only format that is the same all around the world and even computers cannot misunderstood. If you need to use another localized format use it for displaying purpose only. If you follow this rule your life is much easier.
@Pᴇʜ Well, the fact that Oracle is older than the ISO standard, these Oracle specific syntax exist. But, it is recommended to use the globally accepted standards.
Well, true. I should have been more precise and should have said "whenever you can, switch to YYYY-MM-DD" :)

FunThomas · Accepted Answer · 2020-05-19 08:08:52Z

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

Collectives™ on Stack Overflow

SQL Query using two dates

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related