SQL Server remove milliseconds from datetime

Question

select   from table where date  gt   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

User · Answer

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

User · Answer

Please try this  select substring  12 20 19 8470000  1  CHARINDEX      12 20 19 8470000  1 -1      No column name  12 20 19

User · Answer

Try   SELECT    FROM table  WHERE datetime  gt   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

User · Answer

Use  Smalldatetime  data type  select convert smalldatetime  getdate      will fetch  2015-01-08 15 27 00

User · Answer

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

User · Answer

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   gt   2010-07-20 03 21 52

User · Answer

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   gt   CAST CONCAT  YYYYMMDDHHTTSS   000   as DateTime  AND        Time stamp   lt   CAST CONCAT  YYYYMMDDHHTTSS   999   as DateTime     You could use something similar to ignore minutes and seconds too

User · Answer

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    gt   0   or   select   from table where datediff second   2010-07-20 03 21 52   date   gt   0    one less function call  but you have to be beware of overflowing the max integer if the dates are too far apart

User · Answer

May be this will help   SELECT  Datetime    CAST  20120228  AS smalldatetime   o p  2012-02-28 00 00 00

User · Answer

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  gt    2010-07-20 03 21 53

User · Answer

select   from table      where DATEADD ms  DATEDIFF ms   20000101   date    20000101    gt   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

[sql-server] SQL Server remove milliseconds from datetime

Examples related to sql-server

Examples related to tsql