多数据库下按无年份日期筛选并求和的SQL查询问题

Got it, let's work through this problem. Since you're connecting to separate Firebird databases for each calendar year, the main trick here is to map your dd/MM target date to a full year-specific date in each database, then filter rows where DATUM falls before that date. Here are two solid approaches:

方案1: 处理日期在代码中（推荐） #

Since each database corresponds to a single fixed year, you can first determine the year for the current connected database (e.g., parse it from the database name, pull it from config, etc.), then convert the user's dd/MM input into a full DateTime for that year. Use a parameterized query to pass this date to your SQL—this keeps things clean and avoids SQL injection risks.

C# Code Example: #

// Assume you've already fetched the year for the current database (e.g., 2023)
int currentDbYear = 2023;
string userInputDdMm = "05/06"; // User's dd/MM formatted date

// Split the input into day and month components
var dateParts = userInputDdMm.Split('/');
int targetDay = int.Parse(dateParts[0]);
int targetMonth = int.Parse(dateParts[1]);

// Build the full year-specific date
DateTime targetFullDate;
try
{
    targetFullDate = new DateTime(currentDbYear, targetMonth, targetDay);
}
catch (ArgumentOutOfRangeException)
{
    // Handle edge case: e.g., 29/02 in a non-leap year, fall back to last day of the month
    targetFullDate = new DateTime(currentDbYear, targetMonth, DateTime.DaysInMonth(currentDbYear, targetMonth));
}

// Updated parameterized SQL query
string sql = @"
SELECT SUM(UPLACENO) 
FROM DOKUMENT 
WHERE VRDOK = 15 
  AND FLAG = 1 
  AND KODDOK = 0
  AND DATUM < @TargetDate";

// Execute with FbCommand
using (var cmd = new FbCommand(sql, yourFbConnection))
{
    cmd.Parameters.AddWithValue("@TargetDate", targetFullDate);
    var sumResult = cmd.ExecuteScalar();
    // Process your result here
}

Why this works: #

• Keeps date logic in your code where it's easier to debug and handle edge cases
• Parameterized queries are safer and more performant
• Avoids messy string manipulation in SQL

方案2: 动态构造日期在SQL中 #

If you prefer to handle the date mapping directly in SQL, you can use Firebird's date functions to build a year-specific date using the year from the DATUM column itself (since all rows in the database belong to the same year).

Updated SQL Query: #

SELECT SUM(UPLACENO) 
FROM DOKUMENT 
WHERE VRDOK = 15 
  AND FLAG = 1 
  AND KODDOK = 0
  AND DATUM < COALESCE(
    TRY_CAST(
      EXTRACT(YEAR FROM DATUM) || '-' || 
      SUBSTRING(@TargetDdMm, 4, 2) || '-' || 
      SUBSTRING(@TargetDdMm, 1, 2) AS DATE
    ),
    DATE(EXTRACT(YEAR FROM DATUM) || '-12-31')
  )

Breakdown: #

• @TargetDdMm is your input parameter (e.g., '05/06')
• SUBSTRING extracts the month (positions 4-5) and day (positions 1-2) from the input string
• EXTRACT(YEAR FROM DATUM) gets the fixed year for the current database
• TRY_CAST safely converts the concatenated string to a date; if it fails (like 29/02 in a non-leap year), COALESCE falls back to December 31 of the same year (so all rows are included)

内容的提问来源于stack exchange，提问作者Aleksa Ristic