SQL Server remove milliseconds from datetime

问题:

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?

回答1:

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'

回答2:

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)

回答3:

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

回答4:

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'

回答5:

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

回答6:

Use CAST with following parameters:

Date

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

Output: 2017-10-11

Datetime

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

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

SmallDatetime

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

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

DatetimeOffset

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

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

Datetime2

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

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

回答7:

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.

回答8:

Please try this

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


(No column name)
12:20:19

回答9:

Use 'Smalldatetime' data type

select convert(smalldatetime, getdate())

will fetch

2015-01-08 15:27:00

回答10:

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.

回答11:

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

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