SDU Tools: ExcelSerialToDateTime and DateTimeToExcelSerial

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. Excel is fascinating in how it stores data. I have friends who joke that everything in Excel is a number or a string, and anything else you see is an illusion.

Date and time values in Excel are odd and use what most call a serial number when they’re stored. It’s common to need to import values from Excel into SQL Server and to export values to Excel.

Two new functions that we added to SDU Tools a while back, do just that. They are ExcelSerialToDateTime and DateTimeToExcelSerial.

Find out more

You can see these in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below. Note that v24 had one second resolution. v25 will have one millisecond.

CREATE FUNCTION SDU_Tools.ExcelSerialToDateTime
(
    @ExcelSerialValue float
)
RETURNS datetime
AS
BEGIN

-- Function:      Convert an Excel serial value (datetime) to a SQL Server datetime
-- Parameters:    @ExcelSerialValue float
-- Action:        Convert an Excel serial value (datetime) to a SQL Server datetime
-- Return:        datetime
-- Refer to this video: https://youtu.be/hxmGtKjkcwk
--
-- Test examples: 
/*

SELECT SDU_Tools.ExcelSerialToDateTime(44774.250150463);
SELECT SDU_Tools.ExcelSerialToDateTime(44774.25);
SELECT SDU_Tools.ExcelSerialToDateTime(44774);

*/
    RETURN CASE WHEN @ExcelSerialValue IS NOT NULL
                THEN DATEADD(millisecond, 
                        (@ExcelSerialValue - FLOOR(@ExcelSerialValue)) * 60 * 60 * 24 * 1000, -- milliseconds component
                        DATEADD(day, FLOOR(@ExcelSerialValue), '18991230')) -- Excel date floor
           END;
END;
GO

CREATE FUNCTION SDU_Tools.DateTimeToExcelSerial
(
    @ValueToConvert datetime
)
RETURNS float
AS
BEGIN

-- Function:      Convert a SQL Server datetime to an Excel serial value (datetime)
-- Parameters:    @ValueToConvert datetime
-- Action:        Convert a SQL Server datetime to an Excel serial value (datetime)
-- Return:        float
-- Refer to this video: https://youtu.be/hxmGtKjkcwk
--
-- Test examples: 
/*

SELECT SDU_Tools.DateTimeToExcelSerial('2022-08-01 06:00:13.997');
SELECT SDU_Tools.DateTimeToExcelSerial('2022-08-01 06:00:00.000');
SELECT SDU_Tools.DateTimeToExcelSerial('2022-08-01');

*/
    RETURN CASE WHEN @ValueToConvert IS NOT NULL
                THEN CAST(DATEDIFF(day, '18991230', CAST(@ValueToConvert AS date)) AS float)
                     + CAST(DATEPART(hour, @ValueToConvert) * 60.0 * 60.0 * 1000.0
                            + DATEPART(minute, @ValueToConvert) * 60.0 * 1000.0
                            + DATEPART(second, @ValueToConvert) * 1000.0
                            + DATEPART(millisecond, @ValueToConvert) AS float) 
                     / (60.0 * 60.0 * 24.0 * 1000.0)
           END;
END;
GO

2025-02-07