Truncate seconds and milliseconds in SQL

Question

I'm having problems truncating data. I use the SQL function GETDATE () to get the current date and time and enter them into the database. However, I only want to keep the date and time to the minute. In other words, I want dd / mm / yyyy hh: mm: 00.000 or dd / mm / yyyy hh: mm to save when entering new data. How can i do this?

I should note that I am using MS-SQL.

Answer 1

There are several ways to do this.

For example, you can first generate a datetime from GetDate() to smalldatetime , aa:

 CAST(GetDate() AS smalldatetime) 

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

<sub> EDIT: sub>

Alternatively, you can truncate the SQL Server datetime for you to be "clean" (READ: no rounding, since the value is previously truncated), converting to smalldatetime :

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

Answer 2

One way is to convert it to smalldatetime for assignment (and vice versa as needed). smalldatetime always has seconds and cannot be set to 00.

 SELECT CONVERT(smalldatetime, GETDATE()) 

Since this can round up or down, another way to safely crop seconds would be as follows:

 SELECT CONVERT(datetime, CONVERT(nchar(16), GETDATE(), 120), 120) 

conversion code 120 returns the format yyyy-mm-dd hh:mi:ss .

Answer 3

To truncate:

 SELECT SMALLDATETIMEFROMPARTS( datepart(year ,dt) ,datepart(month ,dt) ,datepart(day ,dt) ,datepart(hour ,dt) ,datepart(minute,dt) ) FROM (SELECT GETDATE()) t(dt)