SQL Server: How Do I Count Only Today's Event Rows?

The number that matters is the calendar-day boundary between t_stamp and CURRENT_TIMESTAMP, not the age of the row in hours. Add DATEDIFF(day,t_stamp,CURRENT_TIMESTAMP)=0 to the existing filters to count rows assigned to the SQL Server host's current calendar date. Keep the original fault1 column name; one alternative example changes it to fault, which would address a different column or fail if that column does not exist.

Current-day count

SELECT count(*)
FROM progmode1
WHERE shift = 1
  AND fault1 = 1
  AND mach_sel = 1
  AND DATEDIFF(day, t_stamp, CURRENT_TIMESTAMP) = 0

DATEDIFF returns the number of specified date-part boundaries crossed between two timestamps. With day as the date part, zero means that t_stampand the database server's current timestamp fall on the same calendar date.

This distinction matters near midnight. At 00:05, a row from 23:55 the previous day is only ten minutes old, but it crossed a day boundary and must be excluded. A row from 00:01 crossed no day boundary and remains in the count.

Symptom and cause separation

Observed result	Likely cause	Where to check
Previous-day events remain in the total	The query filters only shift, fault1, and mach_sel	Inspect the complete SQL text sent to the database
Syntax error after adding a date expression	A FactoryPMI property expression or binding token was inserted where SQL syntax was expected	Separate the component expression from the SQL statement
Message similar to “You can not put that type of transact after a where statement”	The expression parser received database syntax, or the SQL parser received application expression syntax	Identify which parser evaluates each field before combining values
The query returns zero unexpectedly	The date filter is correct but another predicate does not match, or t_stamp contains null or unexpected values	Run the predicates incrementally and inspect representative timestamps
The query fails after copying the longer example	fault was substituted for the installation's original fault1	Compare every identifier with the table schema and working query
The date changes earlier or later than operators expect	CURRENT_TIMESTAMP follows the database server's clock and date context	Read the server timestamp and compare it with the displayed application time

Calendar-boundary mechanism

CURRENT_TIMESTAMP is evaluated by SQL Server when the statement runs. The comparison therefore follows the database server's current date rather than the date text displayed by a FactoryPMI component. That removes the need to format a display value, trim it, and inject it into SQL.

The attempted expression rtrim(left(t_stamp,10)) = {root container.current} mixes two evaluation environments. Functions such as RTRIM and LEFT can be database expressions when used in valid SQL, while {root container.current} is an application-side property reference. A raw SQL parser does not interpret that property reference as a date value. Conversely, a property-expression parser does not accept arbitrary SQL clauses.

String extraction is also the wrong mechanism for a timestamp comparison. It makes the result dependent on the timestamp's text representation and the date format of the component value. Comparing date boundaries preserves timestamp semantics and avoids a formatted string comparison.

A null t_stamp does not satisfy the DATEDIFF(...)=0 predicate because the date calculation produces null rather than zero. That behavior normally suits event logs: a row without an event timestamp cannot be assigned to today's count. Investigate null timestamps separately if they indicate incomplete logging.

Query construction procedure

1. Start with the known working statement and retain the installation's identifiers: progmode1, shift, fault1, mach_sel, and t_stamp.
2. Run the original three-predicate query in a SQL Server query frontend. Record its count so the date-filtered result has a baseline.
3. Add AND DATEDIFF(day, t_stamp, CURRENT_TIMESTAMP) = 0. Execute the statement directly against the same database and table used by FactoryPMI.
4. Inspect several qualifying rows rather than trusting the count alone. Temporarily select t_stamp with the same predicates and confirm that each returned timestamp belongs to the server's current date.
5. Configure FactoryPMI to submit the tested SQL statement. Keep component-property expressions outside the SQL unless the binding mechanism explicitly passes them as query parameters.
6. Compare the FactoryPMI result with the direct database result at nearly the same time. Account for new event rows that may arrive between executions.

Incremental construction isolates failures quickly. If the base query works but the fourth predicate fails, troubleshoot the date expression. If both direct SQL versions work but FactoryPMI fails, the fault lies in query construction, binding, or connection context rather than in the date logic.

DATEPART alternative

The same current-date test can compare year, month, and day separately:

SELECT count(*)
FROM progmode1
WHERE shift = 1
  AND fault1 = 1
  AND mach_sel = 1
  AND DATEPART(yy, t_stamp) = DATEPART(yy, CURRENT_TIMESTAMP)
  AND DATEPART(mm, t_stamp) = DATEPART(mm, CURRENT_TIMESTAMP)
  AND DATEPART(dd, t_stamp) = DATEPART(dd, CURRENT_TIMESTAMP)

All three comparisons are required. Comparing only the day number would combine, for example, the same numbered day from different months. Comparing month and day without year would combine matching dates from different years. The DATEDIFF form expresses the same calendar-date decision with one condition and is easier to audit.

Method	Decision quantity	Primary advantage	Recurring concern
DATEDIFF(day,...)=0	Day boundaries crossed	Compact current-day condition	The function operates on t_stamp, which can limit index use on a large log
Three DATEPART comparisons	Equal year, month, and day fields	Each calendar component is explicit	Longer expression with more opportunities for omission
Start-inclusive, next-day-exclusive range	t_stamp at or after day start and before the next day start	Can better support an index on t_stamp	The application or SQL statement must calculate and bind both boundaries correctly

Large-table performance path

The log contains all runtime event days, so execution cost matters as it grows. Both supplied solutions apply a function to t_stamp. SQL Server may need to calculate that function for many candidate rows rather than navigate directly to a contiguous timestamp range.

When response time becomes unacceptable, retain the same calendar semantics but change the predicate to a half-open range:

t_stamp >= day_start
AND t_stamp < next_day_start

day_start represents midnight at the beginning of the selected server date, and next_day_start represents midnight at the beginning of the following date. The inclusive lower bound retains an event recorded exactly at midnight. The exclusive upper bound excludes the next day's midnight without relying on fractional-second precision.

The exact parameter markers and boundary expressions depend on the FactoryPMI query mechanism and SQL Server environment, so read them from the configured binding interface rather than pasting application property syntax into SQL. Measure the execution plan and elapsed time before changing the working expression. An index containing t_stamp can make the range form materially cheaper, but index selection also depends on the other predicates and the distribution of their values.

Result verification

1. Read CURRENT_TIMESTAMP from the same SQL Server connection used for the count. This identifies the date boundary actually controlling the result.
2. Run the filtered count and a detail query using the identical WHERE clause. Sort or inspect the returned t_stamp values to find the earliest and latest qualifying events.
3. Confirm that no timestamp from the prior calendar date appears. Test shortly before and shortly after midnight when practical because that transition exposes boundary and clock-context mistakes.
4. Change one business predicate at a time. Verify shift = 1, then fault1 = 1, then mach_sel = 1, and finally the date condition. This separates a correct zero count from a broken query.
5. Compare the direct SQL result with the FactoryPMI result. If they differ, capture the final SQL text, connection target, server timestamp, and component refresh time.

A live event table can change between checks. For a clean comparison, execute the detail query and count close together, or count the returned detail set from the same captured interval. A small difference caused by newly inserted rows is different from the repeatable inclusion of yesterday's records.

Recurring implementation pitfalls

Calendar-day filtering is sensitive to clock ownership. The query uses the SQL Server host's current timestamp, while an operator may judge “today” from a workstation or application session. If those clocks represent different dates at the time of execution, first choose which clock defines the production day, then compare both displayed values.

A production shift is not automatically the same as a calendar day. The supplied requirement is the current day plus shift = 1. If a shift crosses midnight, this query deliberately divides its records at midnight. A shift-defined reporting window needs explicit start and end timestamps derived from the shift schedule instead.

Formatting a timestamp as text can hide time-zone, language, and conversion issues while adding work to every examined row. Keep t_stamp as a timestamp through the comparison. Format dates only for presentation after the database has selected the correct records.

Future-dated rows on the server's current date also satisfy DATEDIFF(day,t_stamp,CURRENT_TIMESTAMP)=0. If future timestamps should never occur, inspect them as a data-quality or clock-synchronization fault rather than changing the current-date definition silently.

FAQ