.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Calculating yrs, months, days, hours, mins, seconds between two dates. SQL

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :ASP.Net

I'm trying to calculate the yrs, months, days, hours, mins, seconds between two dates with sql server or c# (ive just used sql more). 

I have some code below that I started, but I get some negative values for months, values over 31 for months, and there very well could be more issues. Honestly my math is not the greatest and Im just not sure how to fix it. I want the user to be able to enter their birthday and for it to calculate the yrs, months, days, hours, mins, and seconds they have been alive.

Any help would be great 


((DATEDIFF(MONTH,'10/16/1995',GETDATE()))-(DATEDIFF(YEAR,'10/16/1995',GETDATE())*12)) AS 'Months',
(DATEDIFF(DAY,'10/16/1995',GETDATE()))-(DATEDIFF(MONTH,'10/16/1995',GETDATE())*30) AS 'Days', 
(DATEDIFF(HOUR,'10/16/1995',GETDATE()))-(DATEDIFF(DAY,'10/16/1995',GETDATE())*24) AS 'Hours',
(DATEDIFF(MINUTE,'10/16/1995',GETDATE()))-(DATEDIFF(HOUR,'10/16/1995',GETDATE())*60) AS 'Minutes',
(DATEDIFF(SECOND,'10/16/1995',GETDATE()))-(DATEDIFF(MINUTE,'10/16/1995',GETDATE())*60) AS 'Seconds'

View Complete Post

More Related Resource Links

Calculating percentage of sceduled hours

 Hi all,       I want to calcultae how many employees are scheduled less 24 hrs for each week,b/w 24 and 48 and greater than 48         In excel file i have weekly wise total hours scheduled for each employee  and total no of days scheduled in week(example 6 days and 1 off)which i will upload it to database,based on this i want to display reports  in bound controls Result should be like this                    Scheduled Hrs      8/1/2010        8/8/2010       8/15/2010       8/22/2010                  Less than 24 hrs     5%                       5%                  5%                   5%        Between 24 and 48 hrs    5%         

Algorithm for Computing Age in Years, Months and Days?

I am generating a crystal report.net that compute age of a person through formula. of course, there is the birthdate and current date. I am familiar with the datediff function but I want to compute is like: "18 Years, 2 Months and fifteen days old." it should include the leap year. Any Algorithm that I can apply to crystal report formula? Your help is very appreciated.

MOSS 2007 Calculate Due Date including working hours and working days

Hi, I've searched many forums for an answer to my problem but with very limited success. Basically, I've created a Sharepoint list where I would like to track a resolution of incidents versus SLA timings. I have a column with a receiving date, which is a date when the request was submitted and another column with priority code where I have 3 possible values: low (5 working days to close the request), medium (3 days) and high (1 day). What I would like to do is to automatically calculate due date for solving the incident based on the receiving date and the priority code. The thing is that now it is getting more complicated since I need to exclude saturdays and sundays from calculation, as well as national holidays. Then I would like to take into account working hours (from 8am to 4pm), for example if an incident with medium priority was submitted on Friday 5pm, then it would have its due date set to Wednesday 4pm (the request was sent after working hours so we have 3 full working days to complete it) Not sure if it is feasible to achieve using formulas, maybe some other way?

How do i calculate no of days between two given dates

Dear All, My requirement is used to find no of days between two given dates and time(from date and to date). using c#. Thanx in advance Dinesh        

days between two dates

Is there any way to  get days between two dates and the date format is dd/MM/yyyy i tried many ways but it's not working please help me

How do i calculate no of days between two dates n two times Using C#

Dear All, i got the task to find no of days between two dates n Two given times Using C# I have four fields( columns) Such as follows, Arrival Date, Arrival Time, Leaving Date and Leavingtime. Example: Leaving Date-24/08/2010,Leaving Time- 08:00 , Arrival Date- 12/09/2010 ,Arrival Time-16:00, From the above Example i need to find No of days. Note : Days should calculate from Leaving time to arrival time (with reference to above example it means if he starting at 8:00 o clock(24/08/2010)  in the morning the next day eight o clock will be one day(8:00/ 25/08/2010)) Thanx in advance    

How can I calculate the Number of days between two dates Monday to Friday


    I am using following code for Robert's Posted Code (Url:http://forums.asp.net/t/1142378.aspx) But this is for only calculate weekends saturday and sunday can any one tell me how 
I get Number of days between given two dates Monday to Friday.

private const int MONDAY = 1; private const int TUESDAY = 2; private const int WEDNESDAY = 3; private const int THURSDAY = 4; private const int FRIDAY = 5; private const int SATURDAY = 6; private const int SUNDAY = 7; /// <summary> /// Observe that the method calculates by including the start day, but excludes the end day /// </summary> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> private void GetWeekendDaysBetween(DateTime startDate, DateTime endDate,out int saturdays,out int sundays) { saturdays = -1; sundays = -1; if (endDate < startDate) return; TimeSpan timeBetween = endDate.Subtract(startDate); int weekendsBetween = timeBetween.Days / 7; sundays =

Get Difference between two dates as Hours, Minutes



 I am using the below query to get difference beiween two dates in hours and minutes.Minutes part look ok but the hours is incorrect.

 select datediff(minute,LastDate,getdate()) as "Minutes", datediff(hour,LastDate,getdate()) as "Hours"from emp.

 Note: Using SQL Server 2005


Get number of months from number of days



I have 2 dates (in the form MM/DD/yyyy) representing the start date of the employee and the end date of the fiscal year:

the employee started working on 12/15/2010 and the fiscal year ends on 08/31/2011

I want to calculate the number of months that the employee worked between those 2 dates

in reality this employee will have been working until 08/31/2011 for 8.5 months

datediff function returns 8

how to calculate exact number of months in sql?

thank you in advance

How to calculate working hours betwwen two dates?


Working days - Mon-Fri and Working hours - 9 Am to 5 Pm

Date1                                       Date2                                                Working Hours

Thur, 14-Oct-10 11.30 Am          Thur, 14-Oct-10 5.00 Am                     5.30

Fri,  15-Oct-10 11.30 Am            Mon, 18-Oct-10 5.00 Am                      13.30

Mon,  18-Oct-10 11.30 Am          Mon, 18-Oct-10 6.00 Am                      5.30


Thanks & Regards, PSWA

Months and Years between given two dates.


Hello All,

I have one query.

User will input two dates and I want to print months between those two dates.


  1. Suppose user enters From Date : 1-Apr-2010 and To Date : 15-Oct-2010


So my out put should be like









Please reply me sql query for that one as it is needed on urgent bases.

How to calculate total work hours between dates?

I am in the process of creating a calendar.  This calendar is to track leave hours for employees.  For example if a person takes leave for 2.5 days then the formula would calculate 8hrs per day and 4 hours for half a day = 20hrs.  I have part of the formula I believe "=TEXT([End Time]-[Start Time],"h")".  The problem is that it does not count multiple days and just not working right. Any advice would help.

How to create a calculation in a list - Calculate the number of days between two dates


I have a calculated column in a list called Days Open. This column contains the number of days between today (Sysdate) and another column called the Effective Date. Therefore, if Sysdate is 11/16/2010 and the Effective Date is 10/14/2010, how do I calculate the number of days between the two dates?

I tried using this formula: =TEXT(Sysdate-[Effective Date],"h") and then I tried =INT(Sysdate-Effective Date) & TEXT(Sysdate-Effective Date, ":hh:mm")

Neither formula worked. I hope it is something real simple that I am missing here. Any help you can provide will be highly appreciated!

Events don't show up in a specific month and in last days of months whose calendar view include day



I actually faced a strange problem which is that when I add events in september 2011, they don't show up in the calendar view and the view remains empty but they appear in the all events view but events in other months appear.

Another strange thing is that I was adding events in July 2011. In the calendar view of July 2011, the first 6 days of August appear but this isn't the problem but the problem is that when I add about 3 events in 31 July then about another three from 31 July to 4 August and then about 2 events from 1 to 4 August, the events are collapsed and the 3 more items appear but when I click the link, I don’t see the events that were just on 31 July and I tried this too in the end of March so it seems that the months whose calendar view includes some days from the next month have a problem.

And finally when I change the month to be october 2010, I get error page with the text:

An unexpected erro has occured.

web parts maintenance page: If you have permission, you can use this page to temporarily close Web Parts or remove personal settings. For more information, contact your site administrator.

and I checked in more than one calendar and in more than one site and it's the same, So I'm asking if any one faced something like th

Remote insert speeds query time from 5.5 mins to 3 seconds?



I have a query running in SQL Server 2005 sp2 that takes just over 5½ minutes to run despite my best efforts to speed it up.

It is an INSERT INTO local table SELECT FROM sql statement.  If I just run the SELECT part, it still takes over 5½ minutes.  The query is currently returning 2 rows of data.

However, If I change the table it inserts into to be an identical table on a remote server using a linked server, then it runs in 3 seconds????

I like the time it now takes, but I do not understand why it is so much faster inserting into a table on a remote server.  If anything, I expected it to take a little longer - anyone have any ideas?

These are examples of the query:
--Takes approximately 05:26 to complete
SELECT Field1, Field2, Field3


How to calculate childs age,but when i enter childs age as '03/27/2007' it returns 3 years where as actual age is 2 yr 11 months
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend