Truncate seconds and milliseconds in SQL

Question

I'm having a bit of trouble with truncating data. I'm using SQL's GETDATE() function to get the current date and time and enter them into a database. However, I only want to save the date and time up until the minute. In other words, I want dd/mm/yyyy hh:mm:00.000 or dd/mm/yyyy hh:mm to be saved when I input new data. How can I go about doing this?

I should note I'm using MS-SQL.

Answer 1

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.

_EDIT:

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)

Answer 2

For truncation:

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

Answer 3

One way is to convert it to smalldatetime for the assignment (and back as needed). smalldatetime always has seconds and beyond set to 00.

SELECT CONVERT(smalldatetime, GETDATE())

As this may round up or down, another way to safely truncate the seconds would be this:

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

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

Answer 4

Combine DATEADD and SMALLDATETIME to truncate

CAST(DATEADD(S, -30, dt) AS SMALLDATETIME)

Answer 5

The other option is not sure why you cannot consider the front-end instead of the back-end so don't change the SQL and thus format in the front-end as dd/MM/yyyy HH:mm or whatever format you need if that is doable or appropriate in the context of what you are trying to achieve. For example in an SSRS report you could format the field in question in the report designer or use the Format function. If it is a webpage or Excel I am sure you could also do something similar.