Using Dates from Cell or Named Range in SQL Query

Pass Excel Data Date Range to SQL Query

Consider the following steps for parameterized querying:

SQL

1. Keep your long, intricate SQL in an .sql text file. Format with line breaks and indentation any way you need. Keep in same folder as workbook or centralized location for all users to read in VBA.

2. Remove DECLARE and SET lines, keeping only the single SELECT command.

3. In SELECT query replace every @ variable with qmarks ? for parameterization in VBA.

   WHERE
       SO_Detail.Shpdte_28 BETWEEN ? AND ?
   

VBA

1. Start a VBA Sub subroutine in Workbooks module or standalone module.

2. Set up an ADO ODBC connection to your database. Many online examples and tutorials.

3. Read in SQL query into a string variable:

   ' READ SQL QUERY FROM FILE INTO STRING
   With CreateObject("Scripting.FileSystemObject")
         strSQL = .OpenTextFile("C:\path\to\my\SQL\Query.sql", 1).readall
   End With
   

4. Open an ADO Command object using connection object and query. Create and bind the two date parameters from wherever cells they are located in workbook. Then execute command to render an ADO recordset.

   ' DEFINE COMMAND OBJECT
   
   Set cmd = New ADODB.Command
   With cmd
       .ActiveConnection = conn   ' CONNECTION OBJECT
       .CommandType = adCmdText
       .CommandText = strSQL      ' SQL QUERY
   
       ' BIND DATE PARAMETERS FOR ? IN SQL, ASSUMING startDate AND endDate ARE VBA DATES
       .Parameters.Append .CreateParameter("dtparam1", adDate, adParamInput, , startDate)
       .Parameters.Append .CreateParameter("dtparam2", adDate, adParamInput, , endDate)
   
       ' BIND OUTPUT TO RECORDSET
       Set rs = .Execute
   End With
   

5. Use Range.CopyFromRecordset to output recordset to sheet starting at left most column. Do note: columns do not populate. Loop through recordset Fields for such column names.

   ThisWorkbook.Worksheets("SQLData").Range("A2").CopyFromRecordset rs
   

(In entire VBA code, be sure to specify Dim variables with Option Explicit at very top and incorporate proper error handling that closes recordset and connection and release ASO resources by setting objects to Nothing with or without runtime errors.)

Use date in Excel cell in BETWEEN clause in SQL query

you are missing a space between the date and the time...

Open your VBE and debug print the formula to see the result (maker sure you have the immediate window [view menu / Immediate window).

Sub test()
   Debug.Print Format(Sheet1.Range("B1").Value, "YYYY-MM-DD") & "23:59:59"
End Sub

result
2020-03-1123:59:59

Just can add the space after the DD as follow

Format(Sheet1.Range("B1").Value, "YYYY-MM-DD ") & "23:59:59"

(GOOGLE SHEETS)How do I reference a 'named range' data argument of my QUERY FUNCTION to another cell?

With INDIRECT, i.e.:

=QUERY(INDIRECT(A3),"Select *")

I assume you'll be adding more to the Select clause. Otherwise (i.e. if you are wanting to pull the whole range without conditions), you can just use the INDIRECT function alone:

=INDIRECT(A3)

How to get data range in sql query form excell cells in ODBC source

To return the first day of the month in SQL language use

date_add(date_add(LAST_DAY(now()),interval 1 DAY),interval -1 MONTH)

For the last day of the month

LAST_DAY(now())

So your formula should be:

where REAL_ARRIVAL_DATE between date_add(date_add(LAST_DAY(now()),interval 1 DAY),interval -1 MONTH) and LAST_DAY(now())

In this way, you don't have to change the code every month

Or if you want to change manually the date make a query with parameters.

In excel open connection properties like the picture below

and put in the where clause a question mark

where REAL_ARRIVAL_DATE between ? and ?

Press ok and there will appear 2 popups, that will ask which cell do you want to reference for the dates, like below

Variable named range cell references in Queries

try:

=QUERY({INDIRECT("C"&ROW(C8)&"!A:H")}, 
 "select Col5,Col8 
  where Col3 > date '"&TEXT(C8, "yyyy-mm-dd)&"' 
    and Col4 = 'SALE'
    and Col5 <= "&D8 + (D8*E8)&"
    and Col8 < 1
    and Col8 > 0.01
  order by Col8
  limit 1", 0)

---

Related Topics