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)