14

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.

Ultracoustic
  • 309
  • 2
  • 4
  • 14

5 Answers5

23

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)
László Koller
  • 1,139
  • 6
  • 15
5

For truncation:

SELECT SMALLDATETIMEFROMPARTS(
         datepart(year  ,dt)
        ,datepart(month ,dt)
        ,datepart(day   ,dt)
        ,datepart(hour  ,dt)
        ,datepart(minute,dt)
      ) 
FROM (SELECT GETDATE()) t(dt)
Anon
  • 10,660
  • 1
  • 29
  • 31
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.

KekuSemau
  • 6,830
  • 4
  • 24
  • 34
  • -1 SELECT CAST('1999-12-31 23:59:30' AS smalldatetime) – Anon Jun 30 '14 at 18:31
  • Your selected intermediate conversion format "nchar(16)" is too small, you need at least "nchar(19)" oder seconds would be truncated. – Alexander Powolozki Nov 08 '21 at 10:47
  • @AlexanderPowolozki You might want to read a posting before commenting on it. It literally says "another way to safely truncate the seconds". This is the purpose. – KekuSemau Nov 08 '21 at 11:09
  • 1
    @KekuSemau you are right, i was mentally at my own task, which was to truncate miliseconds but keep seconds. I did not read the posting carefully enought, thank you a lot for your comment. – Alexander Powolozki Nov 08 '21 at 13:06
1

Combine DATEADD and SMALLDATETIME to truncate

CAST(DATEADD(S, -30, dt) AS SMALLDATETIME)
StuKay
  • 307
  • 1
  • 7
0

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.

Glen
  • 802
  • 1
  • 11
  • 27