March 27, 2011 at 1:33 am
Hi All,
Can anyone please let me know how to write a function ... for returning days,hours,minutes and secs when we pass datetime timestamp as input to the function.
I am using these functions datediff(mi,datepart,datepart) , datediff(ss,datepart,datepart) ...etc but I am not getting expected outputs...so I need to write a function which helps me to return days,hrs,minutes and secs provided when datetimestamp is provided.
Please help me ASAP.
March 27, 2011 at 2:05 am
How can u find DIFFERENCE between 2 entities with just having one entity ? DATEDIFF requires 2 dates to find the diff... one date is the one u are currently passing, and is the other one today's date?
March 27, 2011 at 3:34 am
I need to pass two dates as input for the function..sorry i missed that part
March 27, 2011 at 4:54 am
If you don't understand this, just ask.
declare @date1 datetime,
@date2 datetime;
set @date1 = GetDate();
set @date2 = '20110511 08:00:00'; -- start date of sql rally!
-- 24 hrs in a day.
-- 60 minutes in an hour.
-- 60 seconds in a minute.
select [Days] = DateDiff(second, @date1, @date2) / (60*60*24) ,
[Hours] = (DateDiff(second, @date1, @date2) / (60*60)) % 24,
[Minutes] = (DateDiff(second, @date1, @date2) / (60)) % 60,
[Seconds] = (DateDiff(second, @date1, @date2) % 60);
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 27, 2011 at 7:59 am
its giving wrong results
March 27, 2011 at 8:28 am
"start of sql rally", Wayne?! :--)
Made me look up what it is... thanks for using that as an example!
Srividya, I tested Wayne's code with dates other than the start of the sql rally, and it seems to work in every case. Can you post the sample code that is giving you the wrong results?
March 27, 2011 at 8:48 am
Can you take these dates and check :
@date1 = 2011-03-27 20:13:31.640
@date2 = '2011-05-27 00:00:00'
using sql server 2008
Output:
DaysHoursMinutesSeconds
6034629
can you explain me how this is correct for (hrs,mins and secs)..
May be am i missing anything ..?
March 27, 2011 at 9:18 am
Except for the milliseconds part that does seem to be correct to me. From March 27, 2011 at 2:13:31.640 hours to May 26, 2011 at 2:13:31.640 there are exactly sixty days. If you add 3 hours, 46 minutes and 29 seconds, you get to midnight of May 27.
What is the correct output that you would expect?
March 27, 2011 at 10:52 am
sunitabeck (3/27/2011)
"start of sql rally", Wayne?! :--)Made me look up what it is... thanks for using that as an example!
No problem - I figured I'd have a little fun with this example, and in my own way spread the word about the SQL Rally a little bit more.
edit: fixed link
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 27, 2011 at 11:06 am
srividyakudumula (3/27/2011)
Can you take these dates and check :@date1 = 2011-03-27 20:13:31.640
@date2 = '2011-05-27 00:00:00'
using sql server 2008
Output:
DaysHoursMinutesSeconds
6034629
can you explain me how this is correct for (hrs,mins and secs)..
May be am i missing anything ..?
declare @date1 datetime,
@date2 datetime;
set @date1 = '2011-03-27 20:13:31';
set @date2 = '2011-05-27 00:00:00';
select [Add the days] = DateAdd(day, 60, @Date1),
[Add the hours] = DateAdd(hour, 3, DateAdd(day, 60, @Date1)),
[Add the minutes] = DateAdd(minute, 46, DateAdd(hour, 3, DateAdd(day, 60, @Date1))),
[Add the seconds] = DateAdd(second, 29, DateAdd(minute, 46, DateAdd(hour, 3, DateAdd(day, 60, @Date1))));
On my SQL 2008 system, this returns:
Add the days Add the hours Add the minutes Add the seconds
----------------------- ----------------------- ----------------------- -----------------------
2011-05-26 20:13:31.000 2011-05-26 23:13:31.000 2011-05-26 23:59:31.000 2011-05-27 00:00:00.000
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply