SQL Server Parse decimal to DateTime

Question

I have a table in SQL Server 2017. In this table date stores as a decimal in next format:

20180717164540.2200000

YYYYMMDDhhmiss.nnnnnnn

4 digits for the year, 2 digits for a calendar month, 2 digits for a day of a month, 2 digits for a 24-hour based hour of the day in UTC, 2 digits for a minute of an hour, 2 digits for seconds of a minute, and fractional seconds.

My aim is to transform this format into DateTime and store it in another table in a database. So my question is how to convert this format to DateTime format in SQL Server. As far as I know, you can not create custom date format like in Oracle.

I tried a lot of times with FORMAT AND CONVERT MSSQL functions but it not gonna work.

SELECT FORMAT(CONVERT(NVARCHAR(22), DECIMALCOLUMN), 'YYYYMMDDhhmiss') FROM SOURCE-TABLE;

Thank you for your help!

Answer 1

You were really close with your FORMAT idea. FORMAT gives you an NVARCHAR output, which you can explicitly CAST as a DATETIME2(7) (which maintains your precision level).

DECLARE @dateWannaBe DECIMAL(21,7) = 20180717164540.2200000;

SELECT CAST(FORMAT(@dateWannaBe,'####-##-## ##:##:##.#######', 'en-US') AS DATETIME2(7)) AS ActualDateTime2;

+-----------------------------+
|       ActualDateTime2       |
+-----------------------------+
| 2018-07-17 16:45:40.2200000 |
+-----------------------------+

EDIT: Added the culture parameter to the FORMAT function per @JeroenMostert's comment.