Query database to display the total count of data based on current date and month

Question

I want to query my SQL database to display the total count of data in it based on current date and month but my query counts both current and previous date. For instance, if there is 2 entries for 20/08/2018 and 4 entries for 20/07/2018, it returns 6 as total count while I want the query to return only the entries for the current day or month not previous entries.

Here is the query:

            var today = db.QueryValue("select count(*) FROM UserName where DAY(signed_in ) = datepart(DAY, getdate());");
            var month = db.QueryValue("select count(*) FROM UserName where MONTH(signed_in ) = datepart(month,getdate());");

I don't undestand your question very well, but is this your need (or one with AND instead of OR): select count(*) FROM UserName where DAY(signed_in ) = datepart(DAY, getdate()) OR MONTH(signed_in ) = datepart(month,getdate()) — Risto M, Aug 20 '18 at 09:15
If you are looking for current day in month, why you need the date functions? just compare to the date — Y.S, Aug 20 '18 at 09:17
`datepart(DAY, getdate())` picks out the day of the month. If you have 2 entries for 20/08/2018 and 4 entries for 20/07/2018, then that **will** total 6 entries where the day of the month = 20. — Barry Piccinni, Aug 20 '18 at 09:37

score 2 · Answer 1 · answered Aug 20 '18 at 09:30

2

Day Count

select count(*) FROM UserName where DAY(signed_in) = DAY(getdate()) AND MONTH(signed_in) = MONTH(getdate()) AND YEAR(signed_in) = YEAR(getdate());

Month Count

select count(*) FROM UserName where MONTH(signed_in) = MONTH(getdate()) AND YEAR(signed_in) = YEAR(getdate());

answered Aug 20 '18 at 09:30

Rui Fernandes

270
1
11

Why split the date at all for your first count? If you want to compare the day, month and year, then just compare the two dates without separating them out. – Barry Piccinni Aug 20 '18 at 09:34
I think that he don't want to care about the time of those dates and getdate() gives it to you. That's why I did that, or he can cast them to `date` type instead of `datetime`. – Rui Fernandes Aug 20 '18 at 09:39

score 1 · Answer 2 · answered Aug 20 '18 at 09:32

day() extracts the day of the month from a date, it doesn't truncate a date to the day, as you maybe have thought, So of course the first query returns the count for all date where the day of the month is equal to the day of the month of today.

To get only the figures for today try:

SELECT count(*)
       FROM username
       WHERE convert(date, signedin) = convert(date, getdate());

convert()ing to a date drops the time portions (hour of the day, minute, ...).

To get the numbers for the month also include the year. Or you will count for the month in any year.

SELECT count(*)
       FROM username
       WHERE month(signedin) = month(getdate())
             AND year(signedin) = year(getdate());

select count(*) FROM UserName, Meeting where YEAR(signed_in ) = datepart(YEAR, getdate()); if I try to select 2 columns, i get an error message that says, Ambiguous column name 'signed_in'. Please help — Chidi, Aug 29 '18 at 13:58

score 0 · Answer 3 · answered Aug 20 '18 at 10:46

0

A simple way to do these comparisons:

where cast(signed_in as date) = cast(getdate() as date)

where eomonth(signed_in) = eomonth(getdate())

answered Aug 20 '18 at 10:46

Gordon Linoff

1,242,037
58
646
786

Query database to display the total count of data based on current date and month

3 Answers3