March 9, 2009 at 12:45 pm
The Scenario
Both points (Access and SQL) are using the same tables.
The tables exist in SQL 2005 Server.
MSSQL Code
SELECT COUNT(dbo.tbl01.IDFile) AS Expr1
FROM dbo.tbl01 JOIN
dbo.tbl02 ON dbo.tbl01.IDFile = dbo.tbl02.IDFile
WHERE (dbo.tbl02.InsertDate >= GETDATE() - 1)
Access Code
SELECT Count(dbo_tbl01.IDFile) AS Expr1
FROM dbo_tbl01 INNER JOIN dbo_tbl02 ON dbo_tbl01.IDFile = dbo_tbl02.IDFile
WHERE (((dbo_tbl02.InsertDate)>=Date()-1));
Now the results:
SQL: 1199
Access: 1600
Why this difference?
Thks
March 9, 2009 at 12:50 pm
It's the difference between Access' DATE() function and MSSQL's Getdate() function.
Remember - getdate() returns the current date and time whereas DATE() only returns current date.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 9, 2009 at 1:21 pm
Thanks Matt. You're right. Sometimes we have the reason under our nose but we prefer to look to our feets......
March 9, 2009 at 3:41 pm
Here is how you can quickly strip the time off the date in SQL. In your query, just replace @date with your column name.
--------------------------------------------------
declare @date datetime
set @date = getdate()
select DATEADD(dd, DATEDIFF(dd,0, @date),0)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply