![]() ![]() + CASE WHEN DATEPART(SECOND, a.EndDate - a.StartDate) > 0 THEN CAST( DATEPART(SECOND, a.EndDate - a. + CASE WHEN DATEPART(MINUTE, a.EndDate - a.StartDate) > 0 THEN CAST( DATEPART(MINUTE, a.EndDate - a.StartDate) AS nvarchar( 100)) + ' Minutes ' ELSE ' ' END + CASE WHEN DATEPART(HOUR, a.EndDate - a.StartDate) > 0 THEN CAST( DATEPART(HOUR, a.EndDate - a.StartDate) AS nvarchar( 100)) + ' Hours ' ELSE ' ' END We can specify intervals when performing arithmetic against dates and times. Note: This function equals the CURTIME () function. Note: The time is returned as 'HH-MM-SS' (string) or as HHMMSS.uuuuuu (numeric). + CASE WHEN DATEPART(DAY, a.EndDate - a.StartDate) > 1 THEN CAST( DATEPART(DAY, a.EndDate - a.StartDate) - 1 AS nvarchar( 100)) + ' Days ' ELSE ' ' END The CURRENTTIME () function returns the current time. ![]() + CASE WHEN DATEPART(MONTH, a.EndDate - a.StartDate) > 1 THEN CAST( DATEPART(MONTH, a.EndDate - a.StartDate) - 1 AS nvarchar( 100)) + ' Months ' ELSE ' ' END The initial date is so in order to get the correct results, we need to subtract corresponding years, months, and minutes. Remove 0 elements from the output SELECT CASE WHEN DATEPART(YEAR, a.EndDate - a.StartDate) > 1900 THEN CAST( DATEPART(YEAR, a.EndDate - a.StartDate) - 1900 AS nvarchar( 100)) + ' Year ' ELSE ' ' END Now the difference is correct but the date portion contains the date where SQL Server starts to calculate date values. Let's have a look at this using an example: You can read more about this function in Books On Line. This introduces complexity in the calculation since in each date part, the previous date part value needs to be taken into account. Use the parameters hh to specify that you want to use hours and -1 as the amount of hours that you want to add. In other words, if you choose the interval to be minutes a difference is expressed in minutes even if the difference is greater than a single hour. DATEDIFF calculates the whole difference between two dates. For this purpose, we will create a table named WorkTime. There are a ton of examples how to calculate the difference for two datetime values using DATEDIFF function. How to get the number of minutes worked by day of some employees using SQL subtract dates In this example, we will calculate the total minutes worked by 2 customers. ![]() INSERT INTO #TextualDateDiffTest (StartDate, EndDate) Insert test data INSERT INTO #TextualDateDiffTest (StartDate, EndDate) ![]()
0 Comments
Leave a Reply. |