2

How can I eliminate the seconds part in my query. I have used DateADD to show the seconds part as 0 but I just want to ignore it. I only want to show the date hours: minutes I have used this query and it returns me something like this 

SELECT DATEADD(minute, DATEDIFF(minute,0,GETDATE()), 0)

2013-09-30 13:03:00.000

But I want only 2013-09-30 13:03 part.

Can anyone help me?

LaurentG
  • 11,128
  • 9
  • 51
  • 66
Nosheen Javed
  • 175
  • 1
  • 5
  • 21

2 Answers2

4

If you're dealing with a format, then what you have to end up with is a string, not a datetime value - datetimes (or datetime2s) don't have a format.

So you need to convert to a string. There's no format that exactly matches what you're asking for, but if you do a conversion and don't give CONVERT enough space, it truncates the result:

SELECT CONVERT(varchar(16),GETDATE(),120)
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Thank You for your response. My query is running perfect but actually I am inserting it with datetime format and I only have to show in format which in without string so it gives me exception in the code.. – Nosheen Javed Sep 30 '13 at 10:04
  • And for this I have to change the getter setter so that will make issue.. Any way t convert the obtained date back to the datetime?? – Nosheen Javed Sep 30 '13 at 10:08
  • @NosheenJaved - when you have a `datetime` (in SQL or e.g. C#), then as I say, they don't *have* a format. They just have some internal representation (usually purely numeric) - as such those types are fixed and predefined - there's no way to modify the type to be a `datetime-but-in-this-instance-please-ignore-the-seconds`. You need to learn to separate formatting concerns from data storage concerns. – Damien_The_Unbeliever Sep 30 '13 at 10:13
  • Actually I mean that I have done everything and now the clients requirement is changed. I have set the type of the getter setter as datetime so when I run this query it gives me exception now. So I am sorry Is there any way that I convert that obtained varchar to the datetime again so that I don't have to change the getters/setters datatype?? because changing their types will cause a big problem for me – Nosheen Javed Sep 30 '13 at 10:24
  • @NosheenJaved - you can change back to the query you show in your question. As I'm trying to tell you, there's no datatype that is a datetime *without* seconds. So if your requirements are "store in a `datetime`" and "do not show seconds" you've got contradictory requirements - you can't do both. – Damien_The_Unbeliever Sep 30 '13 at 10:32
  • Ok got that now I have to tell this to my client as well.. Thank You soo much for your concern :) – Nosheen Javed Sep 30 '13 at 10:36
  • One more thing sorry.. Is there a way that we convert that varchar back to datetime2 in the query before going into the code?? Something like this... CONVERT (Some Date datetype ,CONVERT(varchar(16),GETDATE(),120)) – Nosheen Javed Sep 30 '13 at 10:40
2

You can't stay in datetime2 format and truncate a part of it.
What you can try one of the following:

SELECT CAST(GETDATE() as date)
SELECT CONVERT(varchar(16),GETDATE(),120)

If this still doesn't give you exactly what you want, you can see many other possible conversions in this post.

Yosi Dahari
  • 6,794
  • 5
  • 24
  • 44
  • The referenced post was deleted 2015-07-09; see [the last archived version on archive.org](https://web.archive.org/web/20150301000000*/http://www.codeproject.com/Articles/576178/cast-convert-format-try-parse-date-and-time-sql). – Pixelstix Dec 12 '22 at 16:19