SQLServer 从日期时中删除毫秒

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

我希望不会给我任何结果... 除了我得到一个日期时间 2010-07-20 03:21:52.577的记录

如何使查询忽略毫秒？

212755 次浏览
小开 
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)
小开

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...

小开
最佳答案

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'
小开

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'
小开

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.

小开

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.

小开

Please try this

select substring('12:20:19.8470000',1,(CHARINDEX('.','12:20:19.8470000',1)-1))




(No column name)
12:20:19
小开

May be this will help.. SELECT [Datetime] = CAST('20120228' AS smalldatetime)

o/p: 2012-02-28 00:00:00

小开

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
小开

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

小开

Use 'Smalldatetime' data type

select convert(smalldatetime, getdate())

will fetch

2015-01-08 15:27:00
小开

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>);
小开

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.


提交答案