SQL Server remove milliseconds from datetime

Question

select * from table where date > '2010-07-20 03:21:52'

which I would expect to not give me any results... EXCEPT I'm getting a record with a datetime of 2010-07-20 03:21:52.577

how can I make the query ignore milliseconds?

If you post (SQL) code or XML, please highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it! — marc_s
– marc_s, Commented Jul 20, 2010 at 4:55

Gabe · Accepted Answer · 2010-07-20 05:18:31Z

116

You just have to figure out the millisecond part of the date and subtract it out before comparison, like this:

select * from table where DATEADD(ms, -DATEPART(ms, date), date) > '2010-07-20 03:21:52'

answered Jul 20, 2010 at 5:18

Gabe

87.1k13 gold badges144 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tmaj Over a year ago

Please mind that this literally removes milliseconds, it does not remove the smaller units, e.g. For '2021-06-14 00:00:00.1234567' select DATEADD(ms, -DATEPART(ms, date), date) produces 2021-06-14 00:00:00.0004567

MMJ · Accepted Answer · 2017-02-18 23:07:59Z

If you are using SQL Server (starting with 2008), choose one of this:

CONVERT(DATETIME2(0), YourDateField)
LEFT(RTRIM(CONVERT(DATETIMEOFFSET, YourDateField)), 19)
CONVERT(DATETIMEOFFSET(0), YourDateField) -- with the addition of a time zone offset

The DATETIME2 option rounds up if > 0.5, the LEFT(RTRIM option truncates. Both are useful

8kb · Accepted Answer · 2010-07-20 04:11:03Z

Try:

SELECT * FROM table WHERE datetime > CONVERT(DATETIME, CONVERT(VARCHAR(20), CONVERT(DATETIME, '2010-07-20 03:21:52'), 120))

Or if your date is an actual datetime value:

DECLARE @date DATETIME SET @date = GETDATE() SELECT CONVERT(DATETIME, CONVERT(VARCHAR(20), @date, 120))

The conversion to style 120 cuts off the milliseconds...

CONVERT(DATETIME, CONVERT(VARCHAR(20), @date, 120)) is a great solution for comparing DateTime values while ignoring milliseconds. Thanks.
VARCHAR(20) was throwing a conversion error for me but I found using VARCHAR(19) instead did the trick

gbn · Accepted Answer · 2010-07-20 03:50:41Z

19

select * from table where DATEADD(ms, DATEDIFF(ms, '20000101', date), '20000101') > '2010-07-20 03:21:52'

You'll have to trim milliseconds before comparison, which will be slow over many rows

Do one of these to fix this:

created a computed column with the expressions above to compare against
remove milliseconds on insert/update to avoid the read overhead
If SQL Server 2008, use datetime2(0)

answered Jul 20, 2010 at 3:50

gbn

434k84 gold badges602 silver badges690 bronze badges

4 Comments

vacip Over a year ago

datetime2 is the best (least expensive) solution IMO

gbn Over a year ago

@vacip: in 2010 there would have been less SQL Server 2008+ and more SQL Server 2005 which did not have datetime2

Northernlad Over a year ago

In my experience datetime2(0) will round up the "2020-07-01 15:16:39.837" to "2020-07-01 15:16:40", so is not the best option.

tmaj Over a year ago

Please note that casting to datetime2(0) will round, not trim.

krads · Accepted Answer · 2021-05-11 18:33:32Z

Use CAST with following parameters:

Date

select Cast('2017-10-11 14:38:50.540' as date)

Output: 2017-10-11

Datetime

select Cast('2017-10-11 14:38:50.540' as datetime)

Output: 2017-10-11 14:38:50.540

SmallDatetime

select Cast('2017-10-11 14:38:50.540' as smalldatetime)

Output: 2017-10-11 14:39:00

Note this method rounds to whole minutes (so you lose the seconds as well as the milliseconds)

DatetimeOffset

select Cast('2017-10-11 14:38:50.540' as datetimeoffset)

Output: 2017-10-11 14:38:50.5400000 +00:00

Datetime2

select Cast('2017-10-11 14:38:50.540' as datetime2)

Output: 2017-10-11 14:38:50.5400000

Good info here (I learnt things) but wondering how do any of these options help the op? All of these keep the result he doesnt want still coming back, or hide it unreliably (as in, potentially omitting results that should come back)

Harold L · Accepted Answer · 2010-07-20 15:48:10Z

12

For this particular query, why make expensive function calls for each row when you could just ask for values starting at the next higher second:

select * from table where date >= '2010-07-20 03:21:53'

answered Jul 20, 2010 at 15:48

Harold L

5,28430 silver badges28 bronze badges

2 Comments

MMJ Over a year ago

It is very likely that the date value is not entered manually, but comes from some variable containing the date, which will be inserted in the query. Therefore, you would need to use functions anyway, as it would be necessary to add 1 second to the time used in the WHERE clause, using for example the function DATEADD(second, 1, date). ;)

Martin Smith Over a year ago

@MMJ - Adding a second to the value with DATEADD is much preferable to using a function on the column itself as it doesn't prevent index use

Prem · Accepted Answer · 2018-11-23 11:19:41Z

3

Use 'Smalldatetime' data type

select convert(smalldatetime, getdate())

will fetch

2015-01-08 15:27:00

edited Nov 23, 2018 at 11:19

answered Jan 8, 2018 at 15:28

Prem

1351 silver badge4 bronze badges

1 Comment

jstuardo Over a year ago

this truncates also seconds which is not what asker wants.

egmfrs · Accepted Answer · 2024-06-03 19:13:15Z

REMOVE milliseconds: (2008+)

CAST(date AS datetime2(0)) -- Good if you want 2024-01-01 12:00:00.000 to become 2024-01-01 12:00:00 (note this rounds up so .500 would've turned it to 12:00:01)

IGNORE milliseconds: (2016+)

DATETRUNC(second, date) -- Turns 2024-01-01 12:00:00.500 into 2024-01-01 12:00:00.000

https://learn.microsoft.com/en-us/sql/t-sql/functions/datetrunc-transact-sql?view=sql-server-ver16#examples

@MartinSmith thanks for the correction. I had admittedly not seen any simple suggestions of datetime2, at least not without a miriad of other instructions/suggestions with it, so I thought I'd be a helping (my sorting is not the default either, which maybe hasn't helped). Personally I think people will arrive here (like me) wanting to omit milliseconds from the datetime (the subject suggests that). DateTrunc will work (2016+) for turning date parts into 0's which the OP may have liked, yes. The op would've been better off appending ".999" to his date string(s) surely to avoid all this fuss! xD

neves · Accepted Answer · 2012-05-10 21:44:16Z

There's more than one way to do it:

select 1 where datediff(second, '2010-07-20 03:21:52', '2010-07-20 03:21:52.577') >= 0

or

select * from table where datediff(second, '2010-07-20 03:21:52', date) >= 0

one less function call, but you have to be beware of overflowing the max integer if the dates are too far apart.

ftexperts · Accepted Answer · 2016-02-20 16:49:10Z

One more way I've set up SQL Server queries to ignore milliseconds when I'm looking for events from a particular second (in a parameter in "YYYY-MM-DD HH:TT:SS" format) using a stored procedure:

 WHERE ...[Time_stamp] >= CAST(CONCAT(@YYYYMMDDHHTTSS,'.000') as DateTime) AND ...[Time_stamp] <= CAST(CONCAT(@YYYYMMDDHHTTSS,'.999') as DateTime)

You could use something similar to ignore minutes and seconds too.

Dave S · Accepted Answer · 2023-10-06 16:45:33Z

0

select CONVERT(datetime, FORMAT(yourdate, 'M/d/yyyy h:m:s'))

answered Oct 6, 2023 at 16:45

Dave S

8487 silver badges8 bronze badges

Comments

Manish Jesani · Accepted Answer · 2016-07-05 09:20:53Z

-1

Please try this

select substring('12:20:19.8470000',1,(CHARINDEX('.','12:20:19.8470000',1)-1)) (No column name) 12:20:19

edited Jul 5, 2016 at 9:20

Manish Jesani

1,3555 gold badges22 silver badges40 bronze badges

answered Jul 5, 2016 at 7:22

imbelani nethengwe

71 bronze badge

Comments

Ian Kemp - SO dead by AI greed · Accepted Answer · 2022-07-20 17:28:28Z

Review this example:

declare @now datetimeoffset = sysdatetimeoffset(); select @now; -- 1 select convert(datetimeoffset(0), @now, 120); -- 2 select convert(datetimeoffset, convert(varchar, @now, 120));

which yields output like the following:

2021-07-30 09:21:37.7000000 +00:00 -- 1 2021-07-30 09:21:38 +00:00 -- 2 2021-07-30 09:21:37.0000000 +00:00

Note that for (1), the result is rounded (up in this case), while for (2) it is truncated.

Therefore, if you want to truncate the milliseconds off a date(time)-type value as per the question, you must use:

declare @myDateTimeValue = <date-time-value> select cast(convert(varchar, @myDateValue, 120) as <same-type-as-@myDateTimeValue>);

Pax Rupert · Accepted Answer · 2022-07-26 18:25:09Z

I'm very late but I had the same issue a few days ago. None of the solutions above worked or seemed fit. I just needed a timestamp without milliseconds so I converted to a string using Date_Format and then back to a date with Str_To_Date:

STR_TO_DATE(DATE_FORMAT(your-timestamp-here, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d %H:%i:%s')

Its a little messy but works like a charm.

Collectives™ on Stack Overflow

SQL Server remove milliseconds from datetime

14 Answers 14

1 Comment

1 Comment

2 Comments

4 Comments

1 Comment

2 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

14 Answers 14

1 Comment

1 Comment

2 Comments

4 Comments

1 Comment

2 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

1 Comment

Linked

Related