+ 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 We can specify intervals when performing arithmetic against dates and times. + CASE WHEN DATEPART(HOUR, a.EndDate - a.StartDate) > 0 THEN CAST( DATEPART(HOUR, a.EndDate - a.StartDate) AS nvarchar( 100)) + ' Hours ' ELSE ' ' END + CASE WHEN DATEPART(DAY, a.EndDate - a.StartDate) > 1 THEN CAST( DATEPART(DAY, a.EndDate - a.StartDate) - 1 AS nvarchar( 100)) + ' Days ' ELSE ' ' END For this purpose, we will create a table named WorkTime. + CASE WHEN DATEPART(MONTH, a.EndDate - a.StartDate) > 1 THEN CAST( DATEPART(MONTH, a.EndDate - a.StartDate) - 1 AS nvarchar( 100)) + ' Months ' ELSE ' ' END 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. The unixepoch() function normally returns an integer number of seconds, but with the optional subsec modifierit will return a floating point number which is the fractional number of seconds. 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 The unixepoch() function returns a unix timestamp - the number of seconds since 00:00:00 UTC. Let's have a look at this using an example: This introduces complexity in the calculation since in each date part, the previous date part value needs to be taken into account. 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. There are a ton of examples how to calculate the difference for two datetime values using DATEDIFF function. INSERT INTO #TextualDateDiffTest (StartDate, EndDate) What would be the syntax to minus 15 mins from current datetime I am using it in WHERE clause For Example Where DateTime GetDate () -15 min It should get the current DateTime & minus 15 mins from it. Insert test data INSERT INTO #TextualDateDiffTest (StartDate, EndDate) Select DATEADD minutes with query SQL Server 2008 (3 answers) Closed 2 years ago.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |