title: Changing date format via VBA to SQL query
body: <p>I have a question which I haven't been able to answer to far, my issue is that I have a macro that is using a SQL lookup to access a SQL database and pull information. Within excel it is always forcing the dates to DD/MM/YYYY (You can force the formatting but when it passes it to the SQL side of things it still comes out as DD/MM/YYYY even if the formatting visually looks correct).</p>
  
  <p>I have tried a number of changes to my code to try and 'force' it however I have had no luck am I over complicating it or is it alot harder to do that it should be? lol.</p>
  
  <p>I will provide my VB code and the "Properties" for what the 'connection' looks like that is made from using the macro.</p>
  
  <p>As a note, the format needs to be YYYY-MM-DD as this is how its stored within the database. Currently the only way to get around it is to use a " ' " infront of the date for instance '2013-12-01 to force it or else it goes to 01/12/2013.</p>
  
  <p>Any ideas? I have been racking my brain for far too long lol.</p>
  
  <p>Regards Jamie</p>
  
  <p>Server is a SQLEXPRESS server if that info is needed.</p>
  
  <p>Code below:</p>
  
  <pre><code>Sub CustomisedSQLQuery()
  '
  ' SQL Query to allow user customisation. 
  '
  
  '
  Dim FileName As String
  Dim User As String
  Dim StartDate As String
  Dim EndDate As String
  Dim Category As String
  
  
  Dim Confirm As Variant
  Confirm = MsgBox("Have you made sure that at least one of the search criteria's is populated? If so your excel may crash or you may kill the database.", vbQuestion + vbYesNo, "Wait....")
  If Confirm = vbNo Then ActiveWorkbook.Sheets("Input Sheet").Activate
  If Confirm = vbNo Then Exit Sub
  
  
  
  FileName = Worksheets("Input Sheet").Cells(10, 1)
  User = Worksheets("Master DATA").Cells(1, 1)
  StartDate = Worksheets("Input Sheet").Cells(10, 3)
  EndDate = Worksheets("Input Sheet").Cells(10, 4)
  Category = Worksheets("Master DATA").Cells(1, 5)
  
  MyStr = Format(StartDate, "yyyy/mm/dd")
  MyStr = Format(EndDate, "yyyy/mm/dd")
  
      Sheets("Output Sheet").Select
      Cells.Select
      Selection.ClearContents
      Range("A1").Select
      With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
          "ODBC;DRIVER=SQL Server;SERVER=SERVERADDRESS;UID=USERNAME;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=ID" _
          , Destination:=Range("$A$1")).QueryTable
          .CommandText = Array( _
          "SELECT DocumentsRead.userID, DocumentsRead.fileName, DocumentsRead.category, DocumentsRead.downloadedByUser, DocumentsRead.timeDownloaded, DocumentsRead.timeR" _
          , _
          "ead" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "FROM EndUsers.dbo.DocumentsRead DocumentsRead" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "WHERE (DocumentsRead.fileName Like '" &amp; FileName &amp; "') AND (DocumentsRead.category='" &amp; Category &amp; "') AND (DocumentsRead.timeRead Is Null) " _
          , "AND (DocumentsRead.timeDownloaded Between {ts '" &amp; StartDate &amp; " 00:00:01'} An" _
          , "d {ts '" &amp; EndDate &amp; " 00:00:01'})")
          .RowNumbers = False
          .FillAdjacentFormulas = False
          .PreserveFormatting = True
          .RefreshOnFileOpen = False
          .BackgroundQuery = True
          .RefreshStyle = xlInsertDeleteCells
          .SavePassword = False
          .SaveData = True
          .AdjustColumnWidth = True
          .RefreshPeriod = 0
          .PreserveColumnInfo = True
          .Refresh BackgroundQuery:=False
      End With
  
  Sheets("Input Sheet").Select
  End Sub
  </code></pre>
  
  <p>This is the connection properties from SQL</p>
  
  <pre><code>SELECT DocumentsRead.userID, DocumentsRead.fileName, DocumentsRead.category, DocumentsRead.downloadedByUser, DocumentsRead.timeDownloaded, DocumentsRead.timeRead
  FROM EndUsers.dbo.DocumentsRead DocumentsRead
  WHERE (DocumentsRead.fileName Like 'GB') AND (DocumentsRead.category='Notices') AND (DocumentsRead.timeRead Is Null) AND (DocumentsRead.timeDownloaded Between {ts '01/12/2013 00:00:01'} And {ts '08/11/2013 00:00:01'})
  </code></pre>
  
  <p>The input sheet looks as follows:</p>
  
  <p><img src="https://i.stack.imgur.com/c1BRf.jpg" alt="Excel"></p>