请求将指定VBA日期处理代码转换为等效SQL语句

Perfect, let's translate that VBA date-handling code into SQL. The original logic calculates the first and last day of the previous month, then formats those dates into uppercase DD-MMM-YYYY strings. Since SQL dialects have different date functions, here's how to replicate this behavior across common databases:

SQL Server #

-- Get first day of previous month (matches VBA's DateSerial(Year(vDt), Month(vDt)-1, 1))
SELECT UPPER(FORMAT(DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0), 'dd-MMM-yyyy')) AS vStartDate;

-- Get last day of previous month (matches VBA's DateSerial(Year(vDt), Month(vDt), 0))
SELECT UPPER(FORMAT(EOMONTH(GETDATE(), -1), 'dd-MMM-yyyy')) AS vEndDate;

-- Combine both in a single query
SELECT 
    UPPER(FORMAT(DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0), 'dd-MMM-yyyy')) AS vStartDate,
    UPPER(FORMAT(EOMONTH(GETDATE(), -1), 'dd-MMM-yyyy')) AS vEndDate;

• DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) calculates the first day of the previous month
• EOMONTH(GETDATE(), -1) directly returns the last day of the previous month
• UPPER() and FORMAT() replicate the VBA UCase(Format(...)) behavior

MySQL #

-- First day of previous month
SELECT UPPER(DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%d-%b-%Y')) AS vStartDate;

-- Last day of previous month
SELECT UPPER(DATE_FORMAT(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%d-%b-%Y')) AS vEndDate;

-- Combined query
SELECT 
    UPPER(DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%d-%b-%Y')) AS vStartDate,
    UPPER(DATE_FORMAT(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%d-%b-%Y')) AS vEndDate;

• DATE_SUB(CURDATE(), INTERVAL 1 MONTH) shifts to the previous month, and DATE_FORMAT handles the string output
• LAST_DAY() grabs the final day of the shifted month

PostgreSQL #

-- First day of previous month
SELECT UPPER(TO_CHAR(DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month'), 'DD-Mon-YYYY')) AS vStartDate;

-- Last day of previous month
SELECT UPPER(TO_CHAR((DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 day'), 'DD-Mon-YYYY')) AS vEndDate;

-- Combined query
SELECT 
    UPPER(TO_CHAR(DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month'), 'DD-Mon-YYYY')) AS vStartDate,
    UPPER(TO_CHAR((DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 day'), 'DD-Mon-YYYY')) AS vEndDate;

• DATE_TRUNC('month', ...) rounds down to the first day of the target month
• To get the last day of the previous month, we take the first day of the current month and subtract one day

Oracle #

-- First day of previous month
SELECT UPPER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1), 'DD-MON-YYYY')) AS vStartDate FROM DUAL;

-- Last day of previous month
SELECT UPPER(TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'DD-MON-YYYY')) AS vEndDate FROM DUAL;

-- Combined query
SELECT 
    UPPER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1), 'DD-MON-YYYY')) AS vStartDate,
    UPPER(TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'DD-MON-YYYY')) AS vEndDate 
FROM DUAL;

• TRUNC(SYSDATE, 'MM') gets the first day of the current month; ADD_MONTHS(..., -1) shifts to the previous month
• LAST_DAY() returns the final day of the shifted month
• Oracle requires FROM DUAL for standalone SELECT statements

内容的提问来源于stack exchange，提问作者B. Thurman