Home Dashboard Directory Help
Search

Datediff overflow error by Nuwan Asiri


Status: 

Closed
 as Won't Fix Help for as Won't Fix


1
0
Sign in
to vote
Type: Bug
ID: 795662
Opened: 8/1/2013 3:33:12 AM
Access Restriction: Public
1
Workaround(s)
view
1
User(s) can reproduce this bug

Description


select datediff(second,getdate()-(100*365),getdate())
datediff cannot handle 100 years when use second as interval
Details
Sign in to post a comment.
Posted by Microsoft on 10/23/2013 at 5:43 PM
Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. Thanks again for reporting the product issue and continued support in improving our product.
Sign in to post a workaround.
Posted by SAinCA on 8/1/2013 at 10:05 AM
This works fine - try it. Returns a bigint, which is the issue with the regular DATEDIFF() function:

USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[udf_GetElapsedSeconds]
    ( @StartDate datetime
    , @EndDate    datetime
    )
RETURNS bigint
WITH SCHEMABINDING
AS
/* =============================================================================
** Author . . . : Stephen L. Anslow
** Create date : 2012-03-05
** Description : Calculates the number of elapsed seconds between two datetime
**                values.
**
** Notes . . . : When 2 dates are subtracted, the actual difference is computed
**                to the nearest 0.003 seconds (SS2005) and expressed in a
**                datetime form that looks like it starts from the constant 0
**                representation of the base date: January 1, 1900.
**                Determine the elapsed difference in a date-time form, then
**                the days different, also in datetime form, and compute the
**                elapsed seconds from the various DATEPART values and the
**                computed days.
**
** Test Examples:
DECLARE @StartDate datetime
DECLARE @EndDate    datetime
SELECT @StartDate = CAST('2010-12-31 23:59:59.997' AS datetime)
     , @EndDate = CAST('2011-01-01 00:00:00.000' AS datetime)
SELECT DBA.dbo.udf_GetElapsedSeconds(@StartDate,@EndDate) AS [0]
SELECT @EndDate = CAST('2011-01-01 00:00:59.997' AS datetime)
SELECT DBA.dbo.udf_GetElapsedSeconds(@StartDate,@EndDate) AS [60]
SELECT @EndDate = CAST('2011-01-01 00:01:59.994' AS datetime)
SELECT DBA.dbo.udf_GetElapsedSeconds(@StartDate,@EndDate) AS [119]
SELECT @EndDate = CAST('2011-01-01 00:01:59.998' AS datetime)
SELECT DBA.dbo.udf_GetElapsedSeconds(@StartDate,@EndDate) AS [120]
SELECT DBA.dbo.udf_GetElapsedSeconds('1/1/1900',GETDATE()) AS [A-Lot]
** ========================================================================== */
/* -----------------------------------------------------------------------------
** -------------------------------------------------------------------------- */
BEGIN
DECLARE @Diff     datetime
DECLARE @Diff_dd bigint
/* -----------------------------------------------------------------------------
** Get difference in date-times relative to datetime(0)
** -------------------------------------------------------------------------- */
SET @Diff    = @EndDate - @StartDate
/* -----------------------------------------------------------------------------
** Number of days difference is again relative to datetime(0), so strip the time
** portion and get just the difference in days.
** -------------------------------------------------------------------------- */
SET @Diff_dd = DATEDIFF(dd,0,DATEADD( day, DATEDIFF( day, 0, @Diff ), 0 ))
/* -----------------------------------------------------------------------------
** Multiply up each "part" and return the total.
** -------------------------------------------------------------------------- */
RETURN ( @Diff_dd         * 84600 ) -- 60 * 60 * 24
        + ( DATEPART(hh,@Diff) * 3600 ) -- 60 * 60
        + ( DATEPART(mi,@Diff) * 60 )
        + ( DATEPART(ss,@Diff) )
END