技术问询：如何用VBScript及SQL查询获取仅早于今日一天的日期

Got you covered on both requests! Let's break this down step by step.

VBScript has a built-in DateAdd function that’s perfect for this—it handles edge cases like month/year boundaries way better than just subtracting a number directly. Here’s a complete snippet:

' Calculate the date one day before today
Dim previousDay
previousDay = DateAdd("d", -1, Date())

' Optional: Format the date to a specific string (e.g., yyyy-mm-dd)
Dim formattedDate
formattedDate = Year(previousDay) & "-" & _
                Right("0" & Month(previousDay), 2) & "-" & _
                Right("0" & Day(previousDay), 2)

' Output the result (adjust this based on how you need to use the date)
WScript.Echo "Yesterday's date: " & formattedDate

• Date() grabs the current system date (no time component)
• DateAdd("d", -1, ...) subtracts one full day from that date
• The formatting part is optional—tweak it to match whatever date format your use case requires.

Looking at your original query, you’re grouping by Address to get the latest transaction time for each. To filter only transactions from the day before today, we need to add a date condition to the WHERE clause. Note that syntax varies a bit by database, so I’ll cover the most common ones:

For SQL Server: #

SELECT Address, MAX(CONVERT(DATETIME, Date + ' ' + time)) AS last_Trans_time
FROM AxA_Transactions
WHERE address IS NOT NULL
  -- Filter for transactions where the Date field is yesterday
  AND CONVERT(DATE, Date) = DATEADD(day, -1, CONVERT(DATE, GETDATE()))
GROUP BY Address

For MySQL: #

SELECT Address, MAX(STR_TO_DATE(CONCAT(Date, ' ', time), '%Y-%m-%d %H:%i:%s')) AS last_Trans_time
FROM AxA_Transactions
WHERE address IS NOT NULL
  -- Filter for transactions where the Date field is yesterday
  AND Date = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY Address

Key notes: #

• I replaced the raw string concatenation of Date + ' ' + time with proper datetime conversion functions (CONVERT/STR_TO_DATE)—this avoids bugs from invalid string formats.
• The date filter uses date-only conversion to ignore any time component, so we’re matching all transactions from the entire previous day. If you instead need transactions that occurred before yesterday (e.g., older than 24 hours from now), adjust the condition to compare the full datetime instead of just the date part.

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