MS Access Date() Function and SQL Server Query

Question

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?

BETWEEN arguments must be listed from smallest to largest. Not that you should be using BETWEEN for range queries anyway. — Aaron Bertrand
– Aaron Bertrand, Commented Feb 9, 2014 at 22:22

M.Ali · Accepted Answer · 2014-02-09 22:48:40Z

2

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;

edited Feb 9, 2014 at 22:48

answered Feb 9, 2014 at 22:02

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

user1457613 Over a year ago

WHERE TransactionTotals.Date=CAST(GETDATE() AS DATE) gets 'Syntax Error (missing operator)

M.Ali Over a year ago

@user1457613 Post the full query in your question.

M.Ali Over a year ago

Your this bit of query is correct there is something else somewhere else you are missing

user1457613 Over a year ago

Well, its the only line being changed but:

user1457613 Over a year ago

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 (TransactionTotals INNER JOIN TransactionItems ON TransactionTotals.EntryID = TransactionItems.EntryID) ON CLIENTS.ClientID = TransactionItems.ClientID

|

Collectives™ on Stack Overflow

MS Access Date() Function and SQL Server Query

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related