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
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.
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
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);
19 People are following this question.
