How to truncate seconds in TSQL?

2020-08-26 11:41发布

问题:

I have time, select cast(SYSDATETIME() AS time) 14:59:09.2834595

What is the way to truncate seconds? 14:59

回答1:

Description

You can use the T-SQL function convert.

Sample

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

will give you hh:mm

More Information

  • MSDN - CAST and CONVERT


回答2:

If you want to truncate seconds and still have a T-SQL Date datatype, first convert the date into minutes from the date '0' and then add the minutes back to '0'. This answer doesn't require any additional parsing/converting. This method works to truncate other parts just change MINUTE. Example: SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, '2016-01-01 23:22:56.997'), 0)



回答3:

If you need to drop seconds off entirely, you can use the DATEPART() function (SQL Server) to strip out the hour and minute, then append it back together. (I like dknaack's solution more, if that works.)

SELECT CAST(DATEPART(hour, SYSDATETIME()) + ':' + DATEPART(minute, SYSDATETIME()) AS DATETIME)


回答4:

select cast(left(cast(SYSDATETIME() AS time), 5) as time)


标签: tsql datetime