I have a SQL Server table that has a datetime column. Its default value is provided by the GETDATE() function.
I want to select the millisecond portion of the value by using DATEPART() function. Interestingly, it is rounding the millisecond portion like 637 to 636. There is a problem.
What is causing the problem or how can I solve it? By using convert function I can exactly handle the millisecond portion but why is the DATEPART function is rounding I couldn't find.
Screen shot of the table query result:
The query
SELECT
[ID],
DATETIME_COLUMN,
DATEPART(ms, DATETIME_COLUMN) MS_PART_W_DATEPART,
RIGHT(CONVERT(VARCHAR,DATETIME_COLUMN,27),3) MS_PART_VARCHAR
FROM
DATETIME_TABLE
You can test with the script and data set.
Table script:
CREATE TABLE [dbo].[DATETIME_TABLE]
(
[ID] [int] NOT NULL,
[DATETIME_COLUMN] [datetime] NULL
) ON [PRIMARY]
Data set:
INSERT INTO [dbo].[DATETIME_TABLE]
([ID], [DATETIME_COLUMN])
VALUES
(613577, '2024-01-01 09:52:06.637'),
(613578, '2024-01-01 09:52:39.050'),
(613579, '2024-01-01 09:53:04.557'),
(613580, '2024-01-01 09:53:36.200'),
(613581, '2024-01-01 09:53:49.307'),
(613582, '2024-01-01 09:53:55.813'),
(613583, '2024-01-01 09:54:01.823'),
(613584, '2024-01-01 09:54:45.277'),
(613585, '2024-01-01 09:55:03.917'),
(613586, '2024-01-01 09:55:27.340'),
(613587, '2024-01-01 09:56:15.700'),
(613588, '2024-01-01 09:57:11.493'),
(613589, '2024-01-01 09:58:10.947'),
(613590, '2024-01-01 09:58:52.040'),
(613591, '2024-01-01 10:00:13.360'),
(613592, '2024-01-01 10:00:59.837'),
(613593, '2024-01-01 10:01:24.680'),
(613594, '2024-01-01 10:01:54.480'),
(613595, '2024-01-01 10:03:37.580'),
(613596, '2024-01-01 10:04:48.683'),
(613597, '2024-01-01 10:05:51.057'),
(613598, '2024-01-01 10:08:00.070'),
(613599, '2024-01-01 10:09:00.627'),
(613600, '2024-01-01 10:10:30.693'),
(613601, '2024-01-01 10:11:23.090'),
(613602, '2024-01-01 10:12:00.417'),
(613603, '2024-01-01 10:12:30.977'),
(613604, '2024-01-01 10:14:46.230'),
(613605, '2024-01-01 10:15:22.373'),
(613606, '2024-01-01 10:16:07.153'),
(613607, '2024-01-01 10:16:44.523'),
(613608, '2024-01-01 10:17:27.480'),
(613610, '2024-01-01 10:50:58.017'),
(613611, '2024-01-01 10:51:20.437'),
(613612, '2024-01-01 10:51:34.000'),
(613613, '2024-01-01 10:51:57.530'),
(613614, '2024-01-01 10:52:15.593'),
(613615, '2024-01-01 10:53:49.623'),
(613616, '2024-01-01 10:54:39.420'),
(613617, '2024-01-01 10:55:10.610'),
(613618, '2024-01-01 10:55:30.093'),
(613619, '2024-01-01 10:55:54.220'),
(613620, '2024-01-01 10:56:08.860'),
(613621, '2024-01-01 10:57:00.830'),
(613622, '2024-01-01 10:57:22.907'),
(613623, '2024-01-01 10:57:35.447'),
(613624, '2024-01-01 10:58:09.763'),
(613625, '2024-01-01 10:58:42.780'),
(613626, '2024-01-01 10:59:00.500'),
(613627, '2024-01-01 10:59:55.590'),
(613628, '2024-01-01 11:00:54.203'),
(613629, '2024-01-01 11:01:31.047'),
(613630, '2024-01-01 11:01:35.657'),
(613631, '2024-01-01 11:01:38.483'),
(613632, '2024-01-01 11:01:41.713'),
(613633, '2024-01-01 11:01:44.360'),
(613634, '2024-01-01 11:01:47.410'),
(613635, '2024-01-01 11:01:54.907'),
(613636, '2024-01-01 11:01:58.360'),
(613637, '2024-01-01 11:40:37.320'),
(613638, '2024-01-01 11:41:40.877'),
(613639, '2024-01-01 11:42:01.297'),
(613640, '2024-01-01 11:42:29.360'),
(613641, '2024-01-01 11:42:51.533'),
(613642, '2024-01-01 11:43:10.940'),
(613643, '2024-01-01 11:43:50.097'),
(613644, '2024-01-01 11:44:26.033'),
(613645, '2024-01-01 11:44:42.317'),
(613646, '2024-01-01 11:45:18.250'),
(613647, '2024-01-01 11:45:45.597'),
(613648, '2024-01-01 11:45:54.957'),
(613649, '2024-01-01 11:46:11.287'),
(613650, '2024-01-01 11:46:36.733'),
(613651, '2024-01-01 11:46:57.487'),
(613652, '2024-01-01 11:47:34.220'),
(613653, '2024-01-01 11:50:25.503'),
(613654, '2024-01-01 11:51:03.487'),
(613655, '2024-01-01 11:51:13.737'),
(613656, '2024-01-01 11:51:28.120'),
(613657, '2024-01-01 11:51:44.520'),
(613658, '2024-01-01 11:51:47.980'),
(613659, '2024-01-01 11:51:56.707'),
(613660, '2024-01-01 11:52:16.320'),
(613661, '2024-01-01 11:52:26.113'),
(613662, '2024-01-01 11:52:29.987'),
(613663, '2024-01-01 11:52:32.940'),
(613664, '2024-01-01 11:52:36.003'),
(613665, '2024-01-01 11:52:43.080'),
(613666, '2024-01-01 11:52:55.503'),
(613667, '2024-01-01 11:53:29.017'),
(613668, '2024-01-01 11:53:55.403'),
(613669, '2024-01-01 11:58:16.183'),
(613670, '2024-01-01 11:58:34.097'),
(613671, '2024-01-01 12:02:31.927'),
(613672, '2024-01-01 12:03:25.807'),
(613673, '2024-01-01 12:08:14.473'),
(613674, '2024-01-01 12:08:17.620'),
(613675, '2024-01-01 12:08:45.040'),
(613676, '2024-01-01 12:09:07.623'),
(613677, '2024-01-01 12:09:13.787')
I tried to solve the problem on my own experience, search the net. Then decide to ask the question.
Result
After reading comments. I added a datetime2 column into the table and update its values from the datetime column.
.7 actually recorded as .66667
datetimeand usedatetime2instead becausedatetimeis lacking accuracy which might result in the ms part to return "unexpected" results learn.microsoft.com/en-us/sql/t-sql/data-types/…datetimedatatype it likes to maintain the idea that values ending7are really.666666recurring. ThisDATEPARTbehaviour was a change under compat 130DATETIMEdatatype - it's accurate only to 3.33ms. No way to change or "fix" that - other than usingDATETIME2(n)datatype instead (which has been introduced in SQL Server 2008 - well over 15 years ago.....) It's really NOTDATEPARTdoing any rounding - it's justDATETIMEwhich is unable to handle millisecond precision -DATETIME2can.DATEPART(ms, '20240101 23:59:59.637')seems to work correctly, as well as:DATEPART(ms, convert(varchar(30),cast('20240101 23:59:59.637' as datetime), 121))7for now, I would suggest casting your column to adatetime2(3)for now, as this then returns7. For example:SELECT DATEPART(MILLISECOND,V.YourColumn) AS DT, DATEPART(MILLISECOND,CONVERT(datetime2(3),V.YourColumn)) AS DT2 FROM (VALUES(CONVERT(datetime,'2024-01-01T09:52:06.637')))V(YourColumn);