I cant seem to find a straight answer to what seems is common scenario. I recently converted an access db to SQL Server, and have linked tables (odbc). Since dates are now being stored as in datetime2 as yyyy-mm-dd hh:mm:ss, i have had no problem in VBA to have my forms write to the database like Format(VBA.Date, "yyyy-mm-dd 00:00:00").
However I have a few simple queries in Access that had a where clause like:
Where Field = Date()
and also
Where Field Between Date() And Date()-7
What is the proper way to query these from an Access query to SQL server?
Your Access Code
Where Field = Date()
Sql Server
Where Field = CAST(GETDATE() AS DATE)
Function GETDATE() gets the current Datetime.
Date between today and last 7 days
Where Field BETWEEN CAST(DATEADD(DAY, -7, GETDATE()) AS DATE)
AND CAST(GETDATE() AS DATE)
The reason I have used CAST() function is because GETDATE() returns the current Datetime something like this 2014-02-09 22:09:53.067
now if you are checking values WHERE Field has the today's date it will only the the values where Field = '2014-02-09 22:09:53.067' Not the records where Date 2014-02-09 it will also take the time into consideration there for I used the CAST Function to get rid of the time part from GETDATE() function.
SELECT GETDATE() Returns '2014-02-09 22:09:53.067'
SELECT CAST(GETDATE() AS DATE) Returns '2014-02-09'
I have fixed the issues with your query there were quite a few of them :S I would suggest reading books online and learn the correct synatx for sql server , see below the fixed query
SELECT TransactionTotals.[Date]
, TransactionTotals.EntryID
, TransactionItems.ItemID
, TransactionItems.ClientID
, [FirstName] + ' ' + [LastName] AS Name
, TransactionItems.[Service]
, TransactionItems.Therapist
, TransactionItems.GiftCertificate
, TransactionItems.Charge
, TransactionItems.Gratuity
, TransactionTotals.Paid
, TransactionItems.Comments
FROM CLIENTS INNER JOIN TransactionItems
ON CLIENTS.ClientID = TransactionItems.ClientID
INNER JOIN TransactionTotals
ON TransactionTotals.EntryID = TransactionItems.EntryID
WHERE TransactionTotals.[Date] = CAST(GETDATE() AS DATE)
ORDER BY TransactionTotals.EntryID DESC;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments