SQL Server remove milliseconds from datetime

select *开发者_JS百科
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?

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'

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

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

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'

Use 'Smalldatetime' data type

select convert(smalldatetime, getdate())

will fetch

2015-01-08 15:27:00

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

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.

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

o/p: 2012-02-28 00:00: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>);