CODEWITHSUNDEEP
mysqlsqljoindate-arithmetic
5ummer5
5ummer5

Reputation: 153

SQL query based on today's date

I have a query which I got help with last night but I am stuck on another bit.

The code I have is

SELECT a.name, COUNT(*) AS num FROM table2 b 
INNER JOIN table1 a 
ON b.status_id=a.id 
GROUP BY status_id

What I would like to do now is only show results if they have been entered on today's date? The date column is in table2. The format for the date column is date and time (eg 1341241153) but I only need to check if the date matches the current day. I hope that is clear.

Any help will be gratefully received!

EDIT: this is a MySQL database. I have tried all the solutions so far and nothings worked. Sorry for not being clearer!!

FINAL EDIT: The question was not clear enough so I posted again in order to get a quick response. The new question with complete solution can be seen here a link

Upvotes: 0

Views: 432

Answers (4)

03Usr
03Usr

Reputation: 3435

On SQL Server 2008 you can use this:

SELECT a.name, COUNT(*) AS num FROM table2 b 
INNER JOIN table1 a 
ON b.status_id=a.id 
WHERE dateValue BETWEEN 
   CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE)) 
GROUP BY status_id

Upvotes: 0

Hermit
Hermit

Reputation: 387

Not sure which database type this is, if it is Oracle you just need to truncate the date:

SELECT a.name, COUNT(*) AS num FROM table2 b 
INNER JOIN table1 a 
ON b.status_id=a.id 
WHERE TRUNC(SYSDATE) = TRUNC(b.dateColumn)
GROUP BY status_id

If it is MySQL or MSSQL the syntax is slightly different, but similarly simple.

Upvotes: 0

juergen d
juergen d

Reputation: 204924

if you groupyou need a having instead of a where:

SELECT a.name, COUNT(*) AS num 
FROM table2 b 
INNER JOIN table1 a 
ON b.status_id=a.id 
GROUP BY status_id
having b.date  = 1341241153

Upvotes: 1

Spikeh
Spikeh

Reputation: 3705

You could create your own function to do this if you do it quite often, but here's some code that flattens any date passed in to it:

SELECT a.name, COUNT(*) AS num
FROM table2 b 
INNER JOIN table1 a ON b.status_id=a.id 
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, a.DateColumn)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
GROUP BY status_id

Upvotes: 0

Related Questions