如何将SQL Server的datetime转换为Excel datetime格式？

Got it, let's tackle converting SQL Server datetime values to Excel's datetime format. I see where your previous attempts ran into issues, so here's how to fix it and get the exact value you need (including both date and time components):

The Simplest Solution #

Excel stores dates as floating-point numbers where:

• The integer part is the number of days since 1899-12-30 (Excel's historical base date)
• The decimal part is the fraction of the day that's passed (e.g., 0.65 = ~15:36, matching your example's rounded value)

In SQL Server, you can directly calculate the difference between your target datetime and Excel's base date, then cast to a decimal with two places to match your examples:

-- Test with your first example value
DECLARE @MyDatetime DATETIME = '2018-08-23 15:32:32.000'
SELECT CAST(@MyDatetime - '1899-12-30' AS DECIMAL(18, 2)) AS ExcelDatetime

-- Test with your second example value
DECLARE @MyDatetime2 DATETIME = '1985-03-26 10:35:42.000'
SELECT CAST(@MyDatetime2 - '1899-12-30' AS DECIMAL(18, 2)) AS ExcelDatetime

What you'll get: #

• For 2018-08-23 15:32:32.000: 43335.65 (the comma in your example is just regional formatting—Excel will recognize the decimal point fine)
• For 1985-03-26 10:35:42.000: 31132.44 (exactly matches your second example)

Why Your Previous Tries Didn't Work #

• DATEDIFF(day, @MyDatetime, GETDATE()) only returns whole days, so it drops the time portion that Excel stores as the decimal part.
• DATEDIFF(second, '1899/12/30', GETDATE()) triggers an overflow because the number of seconds between 1899 and modern dates is over 3.9 billion—way beyond the maximum value of SQL Server's int type (which is ~2.1 billion).

Alternative: Break It Into Date + Time Components #

If you want to see the math explicitly, you can calculate the integer (date) and decimal (time) parts separately:

DECLARE @MyDatetime DATETIME = '2018-08-23 15:32:32.000'

SELECT
    -- Whole days since Excel's base date (integer part)
    CAST(DATEDIFF(day, '1899-12-30', @MyDatetime) AS DECIMAL(18, 2)) +
    -- Fraction of the day elapsed (seconds since midnight / total seconds in a day)
    CAST(DATEDIFF(second, CONVERT(DATE, @MyDatetime), @MyDatetime) AS DECIMAL(18, 2)) / 86400.0 AS ExcelDatetime

This works because:

1. DATEDIFF(day, ...) gives you the whole number of days (the integer part Excel uses).
2. The time calculation takes seconds since midnight, divides by 86400 (total seconds in a day) to get the decimal fraction representing the time.

Either method will give you the Excel-compatible datetime value you're after.

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