Thread: Getting actual date in SQL Query

Good night everyone

I'm trying to get the "now" date instead of a defined date [ (Tab_SERVICO.Data_SRV>={ts '2011-06-30 00:00:00'}) ] in the code below (in bold)
How can I do it? I tried some possible solutions found on the web, but none of them worked.
This code works if I define a data manualy, as it shows.

Sub GetServices()
With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DSN=vd_nacional;Description=vd_nacional;UI D=opvd;PWD=opvd2011;APP=Microsoft Office 2003;WSID=REMOTESERVER2;DATABASE=VD_NACIONAL;Network=DBM"), Array("SSOCN")), Destination:=Range("A1"))
.CommandText = Array("SELECT Tab_SERVICO.Cod_PA, Tab_SERVICO.Data_SRV" & Chr(13) & "" & Chr(10) & "FROM VD_Nacional.dbo.Tab_SERVICO Tab_SERVICO" & Chr(13) & "" & Chr(10) & "WHERE (Tab_SERVICO.Cod_PA Like '520%') AND (Tab_SERVICO.Data_SRV>={ts '2011-06-30 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY T", "ab_SERVICO.Cod_PA")
.Name = "Consulta de vd_nacional"
.FieldNames = True
.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
End Sub

Any help would be apreciated, thanks

After all i was able to solve it after researching some more.
I had to use a DateSerial and Format

The solution:

Sub GetServices()

DataServico = DateSerial(Year(Now), Month(Now), Day(Now))
DataServico = Format(DataServico, "YYYY-MM-DD HH:MM:SS")

With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DSN=vd_nacional;Description=vd_nacional;UI D=opvd;PWD=opvd2011;APP=Microsoft Office 2003;WSID=REMOTESERVER2;DATABASE=VD_NACIONAL;Network=DBM"), Array("SSOCN")), Destination:=Range("A1"))
.CommandText = Array("SELECT Tab_SERVICO.Cod_PA, Tab_SERVICO.Data_SRV" & Chr(13) & "" & Chr(10) & "FROM VD_Nacional.dbo.Tab_SERVICO Tab_SERVICO" & Chr(13) & "" & Chr(10) & "WHERE (Tab_SERVICO.Cod_PA Like '520%') AND (Tab_SERVICO.Data_SRV>={ts '" & DataServico & "'})" & Chr(13) & "" & Chr(10) & "ORDER BY T", "ab_SERVICO.Cod_PA")
.Name = "Consulta de vd_nacional"
.FieldNames = True
.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
End Sub


Thanks anyway!

Originally Posted by DelmarPT

Good night everyone

I'm trying to get the "now" date instead of a defined date [ (Tab_SERVICO.Data_SRV>={ts '2011-06-30 00:00:00'}) ] in the code below (in bold)
How can I do it? I tried some possible solutions found on the web, but none of them worked.
This code works if I define a data manualy, as it shows.

Sub GetServices()
With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DSN=vd_nacional;Description=vd_nacional;UI D=opvd;PWD=opvd2011;APP=Microsoft Office 2003;WSID=REMOTESERVER2;DATABASE=VD_NACIONAL;Network=DBM"), Array("SSOCN")), Destination:=Range("A1"))
.CommandText = Array("SELECT Tab_SERVICO.Cod_PA, Tab_SERVICO.Data_SRV" & Chr(13) & "" & Chr(10) & "FROM VD_Nacional.dbo.Tab_SERVICO Tab_SERVICO" & Chr(13) & "" & Chr(10) & "WHERE (Tab_SERVICO.Cod_PA Like '520%') AND (Tab_SERVICO.Data_SRV>={ts '2011-06-30 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY T", "ab_SERVICO.Cod_PA")
.Name = "Consulta de vd_nacional"
.FieldNames = True
.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
End Sub

Any help would be apreciated, thanks