[sql-server] 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?

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.

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

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'

Please try this

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


(No column name)
12:20:19

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

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

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

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

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)

Use 'Smalldatetime' data type

select convert(smalldatetime, getdate())

will fetch

2015-01-08 15:27:00