Blog Post

How-To Convert Excel Date & Time to SQL

,

Hey data friends! This one comes from my personal vault (aka backlog of drafts I’ve been needing to write up) and is a really simple code that I always forget how to do. So, to save us all some ChatGPT-ing, here’s my tried-and-true way of converting Excel Date & Time fields to a true Date & Time in SQL.

Let’s say you have the following Excel table (don’t worry these people are not real) and are loading it into SQL Server through an ETL process.

When the data gets to SQL, you realize that the call timestamp is coming in as a number! How do you get this to a proper date time field in SQL?

The most accurate way to get the date and time out of this field is to split the field on the period and calculate the date separately from the time. We will use a CTE to split the date and time fields then process the changes.

WITH separate_date_time AS (
SELECT 
*
,SUBSTRING([Call Timestamp],0 --SUBSTRING allows us to get a portion of the text starting at the beginning (the 0) and ending before the '.'
,CHARINDEX('.',[Call Timestamp]))  'CallDate' --CHARINDEX allows us to get the location of the '.' so we know where to end our substring selection
,SUBSTRING([Call Timestamp],CHARINDEX('.',[Call Timestamp])   --for the time, we will start at the '.' and grab everything after that
,LEN([Call Timestamp]))  'CallTime'
FROM dbo.ExcelUploadDemo
)
SELECT 
ID 
,[Call Timestamp]
,CallDate
,CallTime
, DATEADD(D,CAST(CallDate AS INT), '1899-12-30') AS [CleanedCallDate]
, DATEADD(SECOND,ROUND(CAST([CallTime] AS float) * 86400, 0), CAST('00:00' AS TIME)) AS [CleanedCallTime]
, DATEADD(D,CAST(CallDate AS INT), '1899-12-30') + DATEADD(SECOND,ROUND(CAST([CallTime] AS float) * 86400, 0), CAST('00:00' AS TIME)) AS [CleanedCallDateTime]
FROM separate_date_time

Little note, you need to round because occasionally a :00 second value will be converted to 59 seconds of the previous minute. Happy coding friends!

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating