question

529992 avatar image
529992 asked

How to get the date value like '2015/28/07 15:00:00' without using Dateadd and DateDiff function?

DECLARE @MyDate1 DATETIME='2014/04/28 15:15:15'; return DATE(@MyDate1.day,@MyDate1.Month, @MyDate1.year, @MyDate1.hour) I need output like '2014/04/28 15:00:00', Without Using DATEADD and DATEDIFF Functions. Thanks & Regards, Venkat
help
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
You have several answers below. For each of those answers that are helpful you should click on the thumbs up next to the answers so that it turns green. If any one of the answers below lead to a solution to your problem, click on the check mark next to that one answer so that it turns green. This web site works best if you do the voting and marking of answers so that the next person who looks at your question knows what the solution was.
0 Likes 0 ·
KenJ avatar image
KenJ answered
The approach you outline, building a new date from the various date parts, looks something like this: select convert(varchar, datepart(year, @MyDate1)) + '/' -- year + right('0' + convert(varchar, datepart(month, @MyDate1)), 2) + '/' -- month + right('0' + convert(varchar, datepart(day, @MyDate1)), 2) + ' ' -- day + right('0' + convert(varchar, datepart(hour, @MyDate1)), 2) + ':00:00' -- hour plus 0 minutes and seconds A more streamlined way to drop the minutes and seconds would take advantage of the formatting options of the convert function ([ https://msdn.microsoft.com/en-us/library/ms187928.aspx][1]). I added the replace to conform to your desired format with the forward slash: select replace(left(convert(varchar, @MyDate1, 120), 14) + '00:00', '-', '/') If you prefer the output to be a datetime datatype, you can "replace" the replace function with another convert: select convert(datetime, (left(convert(varchar, @MyDate1, 120), 14) + '00:00')) [1]: https://msdn.microsoft.com/en-us/library/ms187928.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Alvin Ramard avatar image
Alvin Ramard answered
I see someone beat me to it, but I was going to suggest: DECLARE @MyDate1 DATETIME='2014/04/28 15:15:15'; SELECT CAST((CAST(CAST(@MyDate1 AS DATE) AS VARCHAR) + ' ' + CAST(DATEPART(HOUR, @MyDate1) AS VARCHAR) + ':00:00') AS DATETIME);
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.