DATE() Not working after BE converted to SQL

Question

I converted my Access Backed to SQL yesterday everything went well except now the Date() function doesn't work. The query below returns no records. If I change the dates to "4/7/2021" it works fine. One thing I noticed is if I put in #4/7/2021# it no longer works. So is there some problem with the translator between Access and SQL server? One other thing all the converted dates were converted to DateTime(0). I created another database and changed all the dates to Date types and connected to it but that didn't fix the issue.

SELECT Package.*, Component.CompName, Component.CompType, Component.BDate, Component.Edate, Package.BDate, Package.EDate
FROM Package INNER JOIN Component ON Package.PkgId = Component.PkgId
WHERE (((Component.BDate)<=Date()) AND ((Component.Edate)>=Date()) AND ((Package.EDate)<=Date()));

SELECT Package.*, Component.CompName, Component.CompType, Component.BDate, Component.Edate, Package.BDate, Package.EDate
FROM Package INNER JOIN Component ON Package.PkgId = Component.PkgId
WHERE (((Component.BDate)<="4/7/2021") AND ((Package.BDate)<="4/7/2021") AND ((Package.EDate)>="4/7/2021"));

Answer 1

If you want to use today's date:

SELECT Package.*, Component.CompName, Component.CompType, Component.BDate, Component.Edate, Package.BDate, Package.EDate
FROM Package INNER JOIN Component ON Package.PkgId = Component.PkgId
WHERE Component.BDate <= CAST(GETDATE() AS date) AND Package.BDate <= CAST(GETDATE() AS date) AND Package.EDate >= CAST(GETDATE() AS date);

If you want to use a specific date:

SELECT Package.*, Component.CompName, Component.CompType, Component.BDate, Component.Edate, Package.BDate, Package.EDate
FROM Package INNER JOIN Component ON Package.PkgId = Component.PkgId
WHERE Component.BDate <= '4/7/2021' AND Package.BDate <= '4/7/2021' AND Package.EDate >= '4/7/2021';

Answer 2

DATE() is not a valid SQL Server function.

Please see:
https://www.databasejournal.com/features/msaccess/article.php/3705151/Converting-Access-Queries-to-SQL-Server.htm

Answer 3

Hi @William Burke ,

Welcome to microsoft TSQL forum!

Your question is related to tsql. My colleague helped you add the tsql tag.
The sql-server-general forum mainly supports:

Date() is not a built-in function of tsql, a function with similar functions in tsql is GETDATE (Transact-SQL)
please refer to:

SELECT Package.*, Component.CompName, Component.CompType,  
CONVERT(DATETIME,Component.BDate,102) ,CONVERT(DATETIME,Component.Edate,102)   
,CONVERT(DATETIME,Package.BDate,102),CONVERT(DATETIME,Package.EDate,102)  
FROM Package INNER JOIN Component ON Package.PkgId = Component.PkgId  
WHERE (((Component.BDate)<=GETDate()) AND ((Component.Edate)>=GETDate()) AND ((Package.EDate)<=GETDate()));  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

You may refer the below for more options:
https://sqlzealots.com/2021/01/15/current_timestamp-getdate-getutcdate-and-sysdatetime-in-sql-server/