I do a lot of data conversion work each month. Many systems store date and time values in Unix format, so I often need to convert to/from these in T-SQL. Fortunately it's easy.
A common way that Unix systems store the date and time value, is to store the number of seconds since the beginning of 1970.
The DateTime2ToUnixTime and UnixTimeToDateTime2 functions in our free SDU Tools for developers and DBAs, does this.
They each take one parameter, which is the value to convert.
Find out more
You can see them 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
CREATE OR ALTER FUNCTION SDU_Tools.DateTime2ToUnixTime ( @ValueToConvert datetime2(0) ) RETURNS bigint AS BEGIN -- Function: Converts a datetime2 value to Unix time -- Parameters: @ValueToConvert datetime2(0) -> the value to convert -- Action: Converts a datetime2 value to Unix time -- Return: bigint -- Refer to this video: https://youtu.be/tGplVv-G3E4 -- -- Test examples: /* SELECT SDU_Tools.DateTime2ToUnixTime('20450101'); */ RETURN DATEDIFF_BIG(second, '19700101', @ValueToConvert); END; GO ------------------------------------------------------------------------------------ CREATE OR ALTER FUNCTION SDU_Tools.UnixTimeToDateTime2 ( @ValueToConvert bigint ) RETURNS datetime2(0) AS BEGIN -- Function: Converts a Unix time to a datetime2 value -- Parameters: @ValueToConvert bigint -> the value to convert -- Action: Converts a Unix time to a datetime2 value -- Return: datetime2(0) -- Refer to this video: https://youtu.be/tGplVv-G3E4 -- -- Test examples: /* SELECT SDU_Tools.UnixTimeToDateTime2(2366841600); */ RETURN DATEADD(day, @ValueToConvert / 86400, DATEADD(second, @ValueToConvert % 86400, '19700101')); END; GO |