---
title: Get database system date
summary: Queries the database server's current date/time through an engine-specific SQL statement, with a local-time fallback on sentinel results.
status: draft
owner: Framework
last_reviewed: 2026-10-02
library: SAIF.NetCore.NHibernate
reference_type: package
operation_id: OP-NH-12
trigger: Consumer reads IRepository<TEntity>.SystemDate or ISessionHelper.SystemDate
---

# Get database system date

This operation provides a database-sourced "now" for consumers that need the server's clock rather than the application server's clock — for example, for audit timestamps.

| Field | Value |
| --- | --- |
| **Operation ID** | OP-NH-12 |
| **Trigger** | Consumer reads `IRepository<TEntity>.SystemDate` (which reads `ISessionHelper.SystemDate`) |
| **Inputs** | — |
| **Outputs** | A `DateTime` from the database server, or the application server's local time as a fallback |
| **Primary components** | `SessionHelperBase.SystemDate`, `OracleSessionHelper`, `SqlServerSessionHelper`, `PostgreSqlSessionHelper` |
| **Entry point** | `src/SAIF.NetCore.Nhibernate/SAIF.NetCore.NHibernate/SAIF.NetCore.NHibernate/SessionHelpers/SessionHelperBase.cs` |

## Steps

1. `SessionHelperBase.SystemDate` runs `Session.CreateSqlQuery(DateSql).UniqueResult()` every time it is read — there is no caching ([BR-NH-17](../business-rules.md#br-nh-17-systemdate-comes-from-the-database-server))
   - `DateSql` is set by the concrete subclass constructor: [SQL-001](../sql/SQL-001-oracle-system-date.md) for `OracleSessionHelper`, [SQL-002](../sql/SQL-002-postgresql-system-date.md) for `PostgreSqlSessionHelper`, [SQL-003](../sql/SQL-003-sql-server-system-date.md) for `SqlServerSessionHelper`.
2. Convert the raw query result with `Convert.ToDateTime(sysDate)`.
3. If the converted result equals `DateTime.MinValue` or `DateTime.MaxValue`: return `DateTime.Now` (application server local time) instead ([BR-NH-18](../business-rules.md#br-nh-18-systemdate-falls-back-to-local-time-on-sentinel-values))
4. Otherwise: return the converted database result.

> **PostgreSQL caveat:** `PostgreSqlSessionHelper.DateSql` is `Select current_date`, which returns a DATE-only value (midnight, no time-of-day component) — unlike Oracle's `sysdate` and SQL Server's `GetDate()`, which both return full date+time. Consumers relying on `SystemDate` for audit timestamps that need time-of-day precision should not use the PostgreSQL engine helper for that purpose.

Note: `SessionHelperBase` also exposes a `UseDatabaseForSystemDate` property, set in the constructor but never read anywhere else in the class or in the `SystemDate` getter — it is a confirmed-unused flag, not merely suspected to be unused.

The fallback is narrowly scoped: it triggers only when `Convert.ToDateTime(sysDate)` **succeeds** and the resulting value equals exactly `DateTime.MinValue` or `DateTime.MaxValue`. A failed conversion (for example a malformed or unexpected query result) or a query/connectivity exception is not caught anywhere in `SystemDate` and propagates to the caller unhandled.

## Process flow

The diagram below visualizes the query/conversion steps and the sentinel-value branch that decides between the database result and the local-time fallback.

```mermaid
flowchart TD
	Start(["SystemDate read"]) --> RunQuery["CreateSqlQuery(DateSql).UniqueResult()"]
	RunQuery --> Convert["Convert.ToDateTime(result)"]
	Convert --> Sentinel{"MinValue or<br>MaxValue?"}
	Sentinel -->|Yes| Fallback(["Return DateTime.Now"])
	Sentinel -->|No| ReturnDb(["Return database result"])
```

## Failure and edge-case behavior
- A database connectivity failure during the query surfaces as whatever exception NHibernate/the ADO.NET driver raises; there is no try/catch around the query itself.
- A `NULL`/sentinel result from the database is masked by the local-time fallback rather than raised as an error, which can hide real data problems.*
- The `UseDatabaseForSystemDate` property is confirmed dead/unused state in the current implementation: it is assigned in the constructor but never read by `SystemDate` or anywhere else in the class.
- On PostgreSQL specifically, when the query returns a successful, non-sentinel result, `SystemDate` returns midnight (date-only); this is a property of the `current_date` SQL expression's successful result, not of the fallback logic. This "always midnight" characterization describes only that successful-result path — it does not describe the sentinel-fallback path (step 3 above), which instead returns `DateTime.Now` (the application server's local time, including time-of-day), nor the unhandled-exception path for a query/conversion failure.

## Related documentation
- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts.

## Sources
- `src/SAIF.NetCore.Nhibernate/SAIF.NetCore.NHibernate/SAIF.NetCore.NHibernate/SessionHelpers/SessionHelperBase.cs`
- `src/SAIF.NetCore.Nhibernate/SAIF.NetCore.NHibernate/SAIF.NetCore.NHibernate/SessionHelpers/OracleSessionHelper.cs`
- `src/SAIF.NetCore.Nhibernate/SAIF.NetCore.NHibernate/SAIF.NetCore.NHibernate/SessionHelpers/SQLSessionHelper.cs`
- `src/SAIF.NetCore.Nhibernate/SAIF.NetCore.NHibernate/SAIF.NetCore.NHibernate/SessionHelpers/PostgreSqlSessionHelper.cs`