Using Dates from Cell or named Range in Sql Query

I have created a sheet to extract data from a Microsoft SQL database to produce a customer report between 2 date StartDate and EndDate.

I have been playing with a few things but have not been successful in anyway. I have searched but have not been able to find anything that was what I was after or able to understand.

The problem I believe is data type of the date I am using in Excel and trying to pass it to the SQL query. I understand I need to convert this in some way to make this possible and correct.

If I manually enter dates into the query it works fine. But not practical for customer use
I am not experience with this and am just! stubbing my way through it. If someone would be so kind to me with this would be much appreciated.

Below is the code I am trying to use

Sub DataExtract()
 '
 DataExtract Macro
 '
 
 ' Create a connection object.
  Dim cni96X As ADODB.Connection
  Set cni96X = New ADODB.Connection
 
 ' Set Database  Range
 
 ' Provide the connection string.
 Dim strConn As String
 Dim Lan As Integer
 Dim OS As Integer
 Dim PointID As String
 
 
 ' Set Variables
 Lan = Range("Lan").Value
 OS = Range("OS").Value
 PointID = Range("PointID").Value
 StartDate = Range("StartDate").Value
 EndDate = Range("EndDate").Value
 
 
 'Use the SQL Server OLE DB Provider.
  strConn = "PROVIDER=SQLOLEDB;"
 
 'Connect to 963 database on the local server.
 strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=i96X;"
 
 'Use an integrated login.
 strConn = strConn & " INTEGRATED SECURITY=sspi;"
 
  'Now open the connection.
 cni96X.Open strConn
 
 ' Create a recordset object.
 Dim rsi96X As ADODB.Recordset
 Dim rsi96X1 As ADODB.Recordset
 Set rsi96X = New ADODB.Recordset
 Set rsi96X1 = New ADODB.Recordset
 
 With rsi96X
     ' Assign the Connection object.
      .ActiveConnection = cni96X
     ' Extract the required records1.
     .Open "SELECT ModuleLabel, originalAlarmTime FROM LastAlarmDetailsByTime WHERE (os = " & OS & " And theModule = N'" & PointID & "'AND AlarmCode = N'DI=1' And lan = " & Lan & " And originalAlarmTime BETWEEN N'" & StartDate & "' AND N'" & EndDate & "') ORDER BY originalAlarmTime DESC"
     ' Copy the records into sheet.
     Range("PointLabel, TimeCallInitiated").CopyFromRecordset rsi96X
 
 
 With rsi96X1
     .ActiveConnection = cni96X
     ' Assign the Connection object.
     .Open "SELECT originalAlarmTime FROM LastAlarmDetailsByTime WHERE (os = " & OS & " And theModule = N'" & PointID & "'AND AlarmCode = N'CDI1' And lan = " & Lan & " And originalAlarmTime BETWEEN N'" & StartDate & "' AND N'" & EndDate & "')ORDER BY originalAlarmTime DESC"
      ' Copy the records into sheet.
     Sheet1.Range("TimeCallEnded").CopyFromRecordset rsi96X1
     ' Tidy up
     .Close
 

I hope this makes sense.

---