如何将SQL Server 17中十进制日期转换为Excel原显示格式

Got it, let's work through this problem step by step to get your original Excel date format back from that weird numeric string stored in SQL Server.

First, let's demystify that value 29221.6075462963: it's an Excel date serial number. The integer part counts days starting from January 1, 1900, and the decimal part represents the fraction of the day (so 0.5 would be noon, for example).

Since your field is stored as nvarchar(200), we need to convert it through a few stages to get back the 1/1/1980 2:34:52 PM format you need.

Final Query #

Assuming your table is named YourTable and the column holding the serial number is ExcelDateString, here's the query you can use:

SELECT 
    FORMAT(
        DATEADD(DAY, CAST(ExcelDateString AS FLOAT) - 1, '1900-01-01'),
        'MM/dd/yyyy h:mm:ss tt',
        'en-US'
    ) AS OriginalExcelDateTime
FROM YourTable;

Breakdown of Each Step #

• CAST(ExcelDateString AS FLOAT): Turns the string version of the serial number into a numeric value we can use for date calculations.
• DATEADD(DAY, CAST(...) - 1, '1900-01-01'): Excel uses 1 to represent 1900-01-01, so we subtract 1 from the serial number and add those days to the starting date. The decimal part automatically translates to the time portion in SQL Server's datetime type.
• FORMAT(..., 'MM/dd/yyyy h:mm:ss tt', 'en-US'): Formats the converted datetime into exactly the style you need. The tt gives you AM/PM, and specifying en-US ensures regional settings don't mess up the format.

For Older SQL Server Versions (Pre-2012) #

The FORMAT function was added in SQL Server 2012. If you're on an older version, use CONVERT instead with this query:

SELECT 
    CONVERT(VARCHAR(10), DATEADD(DAY, CAST(ExcelDateString AS FLOAT)-1, '1900-01-01'), 101) 
    + ' ' + 
    RIGHT('0' + LTRIM(RIGHT(CONVERT(VARCHAR(25), DATEADD(DAY, CAST(ExcelDateString AS FLOAT)-1, '1900-01-01'), 109), 14)), 11)
AS OriginalExcelDateTime
FROM YourTable;

Here, style 101 gets you the MM/dd/yyyy date format, and style 109 pulls in the time with AM/PM. We concatenate them to match the original Excel format.

Quick Notes #

• If there's a chance some values in ExcelDateString aren't valid numbers, swap CAST with TRY_CAST—this will return NULL for invalid entries instead of throwing an error.
• Double-check that your date falls after March 1, 1900 if you ever deal with earlier dates—Excel has a known bug where it treats 1900 as a leap year (it's not), but that won't affect your 1980 date.

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