CODEWITHSUNDEEP
sqlsql-servert-sql
optionsix
optionsix

Reputation: 956

Date grouping in a simple sql query

I need some help with a SQL query. I have a table in a SQL server database with three fields. Field1, Field2 and DateField. I want to do a count of how many records at any given time are returned where Field1 matches a set variable, Field2 matches a set variable, and the DateField is on the same day (24 hr day from 12:00am - 23:59pm)

select count(*)
from TableA
where Field1 = 'A'
and Field2 = 'B'
and DateField = TODAY

I need help with the date grouping. Any help would be appreciate.

Upvotes: 1

Views: 50

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415780

In a nutshell:

and DateField >= TODAY and DateField < TOMORROW

specifically:

and DateField >= cast(current_timestamp as date) and DateField < cast(dateadd(dd, 1, current_timestamp) as date)

You can also use BETWEEN, but I prefer the exclusive upper bound. If find that approach less prone to mistakes, such as using 11:59pm as the end time and leaving records from 23:59.001 to 23:59.997 without a home, or matching on records that actually fall on midnight tomorrow.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Is this what you want?

select count(*)
from TableA
where Field1 = 'A' and Field2 = 'B' and
      DateField = cast(getdate() as date);

Upvotes: 1

Related Questions