SQL statement with ¸variable date

[Guitarde]

Hello everyone,

I am trying to extract the invoice listing of the day from our SQL server.
With the following sql statement it works ok (date greater than is set in the code);

stSQL = "SELECT t207_invoicing.date_maj, t207_invoicing.invoicing_invoice_number " & _
"FROM D73JRD.dbo.t207_invoicing t207_invoicing " & _
"WHERE (t207_invoicing.date_maj>={ts '2011-04-26 00:00:00'})"


What I want to extract is any invoices made today, so I am trying to include today's date into a variable an pass it into the sql criterias. I have tried several different ways but I always get some errors at the time of date extraction.


Dim vDateMin As date
vDateMin = Year(Now()) & "-" & Month(Now()) & "-" & Day(Now())

stSQL = "SELECT t207_invoicing.date_maj, t207_invoicing.invoicing_invoice_number " & _
"FROM D73JRD.dbo.t207_invoicing t207_invoicing " & _
"WHERE (t207_invoicing.date_maj>={ts '" & vDateMin & "'})"



I am using the code of XL Dennis for the query / SQL tester.

I tried several ways, with and without the ' , ts or }.
Please advise if you have any ideas to solve my sql statement.


Thanks
Eric

 

[Richard Schollar]

Hi Eric - you just need to format today's date:

stSQL = "SELECT t207_invoicing.date_maj, t207_invoicing.invoicing_invoice_number " & _
"FROM D73JRD.dbo.t207_invoicing t207_invoicing " & _
"WHERE (t207_invoicing.date_maj>={ts '" & Format(Date,"yyyy-mm-dd") & " 00:00:00'})"

 

[Guitarde]

Hello Richard,

It works great with your help.

Thanks!
Eric