question

WilliamBurke-1905 avatar image
0 Votes"
WilliamBurke-1905 asked EchoLiu-msft commented

DATE() Not working after BE converted to SQL

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"));
sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have any update?
Please also remember to accept the answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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';
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft rolled back

Hi @WilliamBurke-1905,

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:
85583-image.png

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.




image.png (16.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQLZealots avatar image
0 Votes"
SQLZealots answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.