Will DATETIME(3) data type work for Microsoft SQL Server?-CodePudding

For MySQL, to specify the data type date time, then you must use the keyword DATETIME. But it will only give you normal time. Not milliseconds.

mysql> SELECT col,
     >     CAST(col AT TIME ZONE INTERVAL ' 00:00' AS DATETIME) AS ut
     >     FROM ts ORDER BY id;
 --------------------- --------------------- 
| col                 | ut                  |
 --------------------- --------------------- 
| 2020-01-01 10:10:10 | 2020-01-01 15:10:10 |
| 2019-12-31 23:40:10 | 2020-01-01 04:40:10 |
| 2020-01-01 13:10:10 | 2020-01-01 18:10:10 |
| 2020-01-01 10:10:10 | 2020-01-01 15:10:10 |
| 2020-01-01 04:40:10 | 2020-01-01 09:40:10 |
| 2020-01-01 18:10:10 | 2020-01-01 23:10:10 |
 --------------------- ---------------------

To get in milliseconds, one must use DATETIME(3) and for microseconds, one must use DATETIME(6).

But for Microsoft SQL Server, then DATETIME will give you hh:mm:ss:mmm by default. https://www.mssqltips.com/sqlservertip/5993/sql-server-date-and-time-functions-with-examples/

Question:

What will happen if I used DATETIME(3) in Microsoft SQL Server? Will I get an error, or will it give me the time stamp hh:mm:ss:mmm as it was default?

I don't have Microsoft SQL Server. Only MySQL, but I'm trying to make my C code compatible with both SQL servers.

CodePudding user response：

By default datetime datatype returns with precision of milliseconds. The milli second values are rounded to increments of .000, .003, or .007 seconds.Datetime

SELECT GETDATE()

2022-01-31 03:02:58.997

But, if you want to specify the precision, you can go with datetime2 datatype. Datetime2 is ANSI standard and it is accurate to 100 nano seconds.

DECLARE @datetime2literal datetime2(3) = '2022-01-10 10:22:33.1234567'
SELECT @datetime2literal

2022-01-10 10:22:33.123

I would suggest you to go with DATETIME2 as it is coming with less storage with more precision and also ANSI Standard. DateTime2

Advantages of Datetime2
Reference article

larger date range
larger default fractional precision
optional user-specified precision
higher accuracy, even when using using the same number of decimal places as datetime (i.e. 3)
less storage size when using using the same number of decimal places as datetime, yet with higher accuracy* the option to use 2 bytes less storage than datetime (albeit with lower precision)*
aligns with the SQL standards (ANSI & ISO 8601)