CODEWITHSUNDEEP
sqlsql-serverdatedatetime
Ultracoustic
Ultracoustic

Reputation: 309

Truncate seconds and milliseconds in SQL

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.

Upvotes: 14

Views: 38013

Answers (5)

Glen
Glen

Reputation: 830

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.

Upvotes: 0

StuKay
StuKay

Reputation: 317

Combine DATEADD and SMALLDATETIME to truncate

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

Upvotes: 1

László Koller
László Koller

Reputation: 1159

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)

Upvotes: 24

Anon
Anon

Reputation: 10908

For truncation:

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

Upvotes: 5

KekuSemau
KekuSemau

Reputation: 6856

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.

Upvotes: 3

Related Questions