SQL statement in excel verbinding

[Biljartbal100]

Ik heb onderstaande code voor een verbinding naar een database tabel. Ik wil de code graag zo gewijzigd hebben dat ik een specifiek jaar eruit kan selecteren.

SELECT customerinvoicelineoverview_0.Id, customerinvoicelineoverview_0.InvoiceNumber, customerinvoicelineoverview_0.PaymentStatus, customerinvoicelineoverview_0.PaymentStatusId, customerinvoicelineoverview_0.CustomerId, customerinvoicelineoverview_0.CompanyName, customerinvoicelineoverview_0.Address, customerinvoicelineoverview_0.City, customerinvoicelineoverview_0.ZipCode, customerinvoicelineoverview_0.Country, customerinvoicelineoverview_0.InvoiceDate, customerinvoicelineoverview_0.ProductId, customerinvoicelineoverview_0.ProductCode, customerinvoicelineoverview_0.ProductName, customerinvoicelineoverview_0.Quantity, customerinvoicelineoverview_0.QuantityFree, customerinvoicelineoverview_0.Unit, customerinvoicelineoverview_0.Price, customerinvoicelineoverview_0.Discount, customerinvoicelineoverview_0.VatPercentage, customerinvoicelineoverview_0.TotalExVat, customerinvoicelineoverview_0.Line, customerinvoicelineoverview_0.PriceUnit, customerinvoicelineoverview_0.Memo, customerinvoicelineoverview_0.DefaultProductGroupId, customerinvoicelineoverview_0.PurchasePrice, customerinvoicelineoverview_0.CombinationName, customerinvoicelineoverview_0.ProductAttributeCombinationId, customerinvoicelineoverview_0.AttributesPrice, customerinvoicelineoverview_0.AttributesPriceInclVat, customerinvoicelineoverview_0.PriceInclVat, customerinvoicelineoverview_0.TotalInclVat, customerinvoicelineoverview_0.AttributeName, customerinvoicelineoverview_0.ProductGroupName, customerinvoicelineoverview_0.Industry, customerinvoicelineoverview_0.SalesAgent
FROM brickx_natusor.customerinvoicelineoverview customerinvoicelineoverview_0
WHERE (customerinvoicelineoverview_0.InvoiceDate>=DATE('2019-1-1'))

Groet Biljartbal.

 

[Excelbat]

DATE('2019-1-1') wijzigen in DATEPART(YEAR, '2019-1-1')=2019

Greetz/Excelbat

 

[Biljartbal100]

Ik heb nu onderstaande. Excel komt met de melding dat de sql synatx niet goed is.

SELECT customerinvoicelineoverview_0.Id, customerinvoicelineoverview_0.InvoiceNumber, customerinvoicelineoverview_0.PaymentStatus, customerinvoicelineoverview_0.PaymentStatusId, customerinvoicelineoverview_0.CustomerId, customerinvoicelineoverview_0.CompanyName, customerinvoicelineoverview_0.Address, customerinvoicelineoverview_0.City, customerinvoicelineoverview_0.ZipCode, customerinvoicelineoverview_0.Country, customerinvoicelineoverview_0.InvoiceDate, customerinvoicelineoverview_0.ProductId, customerinvoicelineoverview_0.ProductCode, customerinvoicelineoverview_0.ProductName, customerinvoicelineoverview_0.Quantity, customerinvoicelineoverview_0.QuantityFree, customerinvoicelineoverview_0.Unit, customerinvoicelineoverview_0.Price, customerinvoicelineoverview_0.Discount, customerinvoicelineoverview_0.VatPercentage, customerinvoicelineoverview_0.TotalExVat, customerinvoicelineoverview_0.Line, customerinvoicelineoverview_0.PriceUnit, customerinvoicelineoverview_0.Memo, customerinvoicelineoverview_0.DefaultProductGroupId, customerinvoicelineoverview_0.PurchasePrice, customerinvoicelineoverview_0.CombinationName, customerinvoicelineoverview_0.ProductAttributeCombinationId, customerinvoicelineoverview_0.AttributesPrice, customerinvoicelineoverview_0.AttributesPriceInclVat, customerinvoicelineoverview_0.PriceInclVat, customerinvoicelineoverview_0.TotalInclVat, customerinvoicelineoverview_0.AttributeName, customerinvoicelineoverview_0.ProductGroupName, customerinvoicelineoverview_0.Industry, customerinvoicelineoverview_0.SalesAgent
FROM brickx_natusor.customerinvoicelineoverview customerinvoicelineoverview_0
WHERE (customerinvoicelineoverview_0.InvoiceDate DATEPART(YEAR, '2019-1-1'))=2019

Groet Biljartbal.

 

[Excelbat]

Je vergeet = (het = teken)

Greetz/Excelbat

 

[lam201]

Ik dacht zo:

WHERE YEAR(customerinvoicelineoverview_0.InvoiceDate) = 2019

 

[Excelbat]

Dus:
=DATEPART(YEAR, '2019-1-1')

 

[Biljartbal100]

Dank je Excelbat en Lam201 voor de genomen moeite. De oplossing van Lam201 werkt waarvoor mijn dank!

Lam201, leeft deze informatie alleen in jou hoofd of kan ik dat ook ergens nalezen?

Gr. Biljartbal

 

[snb]

Terug naar je oorsponkelijke vraag:

... WHERE year(customerinvoicelineoverview_0.InvoiceDate) > 2018"

Heb je overwogen:

"Select * FROM brickx_natusor.customerinvoicelineoverview customerinvoicelineoverview_0 WHERE year(customerinvoicelineoverview_0.InvoiceDate) > 2018"

Leer je allemaal hier:

https://www.w3schools.com/asp/ado_intro.asp