Truncate Seconds and Milliseconds in SQL

Truncate seconds and milliseconds in SQL

There are a number of ways to go about doing this.

For example, you could convert the generated datetime from GetDate() to a smalldatetime first, à la:

CAST(GetDate() AS smalldatetime)

To be clear, this will round the generated seconds up (or down) to the nearest minute depending up the value of the current second.


Alternatively, you can have SQL Server truncate a datetime for you for a "cleaner" (READ: no rounding, since the value is pre-truncated) conversion to smalldatetime:

CAST(DateAdd(minute, DateDiff(minute, 0, GetDate()), 0) AS smalldatetime)

Truncate Datetime to Second (Remove Milliseconds) in T-SQL

This will truncate the milliseconds.

declare @X datetime
set @X = '2012-01-25 17:24:05.784'
select convert(datetime, convert(char(19), @X, 126))


select dateadd(millisecond, -datepart(millisecond, @X), @X)




How to truncate seconds in TSQL?


You can use the T-SQL function convert.


PRINT convert(varchar(5), SYSDATETIME(), 108)

will give you hh:mm

More Information


Ignore seconds and milliseconds FROM GETDATE() in SQL

I'd either use the DATEADD/DATEDIFF trick that Codo has shown or just cast it to smalldatetime1:

select CAST(GETDATE() as smalldatetime)

I'd avoid anything that involves round-tripping the value through a string.

1It may be appropriate, at this time, to change your schema to use this data type anyway, if seconds are always irrelevant.

Remove milliseconds from date time field in SQL Server 2008

To remove milliseconds, you can use smalldatetime: cast(login as smalldatetime)

To remove seconds as well, use datetime2(0): cast(login as datetime2(0))

SQL Server remove milliseconds from datetime

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'

Truncate hive-1.0 timestamp to seconds and change format from "yyyy:mm:dd:ss.SSS" to "yyyy:mm:dd:ss"

Firt of all, correct timestamp format is

yyyy-MM-dd HH:mm:ss.SSSSSS

By default timestamp without milliseconds will be printed with .00 or .0 at the end. Because it is string representation of timestamp. If you want it to be without milliseconds part, convert it to string and specify format explicitly or just use substring to get value without milliseconds. substr() will work correctly with timestamps as well as string literals also. This is the simplest way if you only want to remove milliseconds without changing everything else:

select substr('2020-01-01 12:10:10.123',1,19)


2020-01-01 12:10:10

How do I truncate down timestamp in Bigquery

Related Topics

Leave a reply