0

I wanted to remove/ignore the seconds and milliseconds coming from GETDATE() SQL function. When I executed, 

SELECT GETDATE()

output will be like

2015-01-05 14:52:28.557

I wanted to ignore seconds and milliseconds from above output. What is the optimize and best way to do this?

I have tried to do this by typecasting like this:

SELECT CAST(FORMAT(GETDATE(),'yyyy-MM-dd HH:mm:0') AS datetime)

Is it is the correct and optimize way to do this?

Geeky Ninja
  • 6,002
  • 8
  • 41
  • 54
  • What do you actually want to do? Simply format it in a particular way? – shree.pat18 Feb 05 '15 at 07:01
  • possible duplicate of [A way to extract from a DateTime value data without seconds](http://stackoverflow.com/questions/8896663/a-way-to-extract-from-a-datetime-value-data-without-seconds) – CJBS Feb 05 '15 at 07:07
  • @ shree.pat18 I have a datetime DB column in which till minutes date is saving in database. Now, I wanted compare it with current system date, which I'm getting from getdate function. – Geeky Ninja Feb 05 '15 at 07:28

3 Answers3

1

Try this:

SELECT dateadd(minute, datediff(minute, 0, GETDATE()), 0)

The query uses the fact that DATEDIFF return the number of minutes between two dates, ignoring the smaller units. 0 is a fixed date in the past.

It can be easily adapted to other time units.

Codo
  • 75,595
  • 17
  • 168
  • 206
1

I'd either use the DATEADD/DATEDIFF trick that Codo has shown or just cast it to smalldatetime1:

select CAST(GETDATE() as smalldatetime)

I'd avoid anything that involves round-tripping the value through a string.

1It may be appropriate, at this time, to change your schema to use this data type anyway, if seconds are always irrelevant.

Community
  • 1
  • 1
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
0

I think your way is fine if it works (looks like it should, but I haven't tested it.) There are lots of other possible approaches, too. Here's one:

select cast(convert(char(16), getdate(), 120) as datetime)
radshop
  • 602
  • 5
  • 19