Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

datediff on LAG expression error - The datediff function resulted in an overflow

Query:
; with t as
(      SELECT [ID],
                     [LastRunTime],
                     LAG([LastRunTime], 1,0) OVER (ORDER BY [LastRunTime])  AS LAGLastRunTime
              , .....
       FROM .....
)
SELECT *, DATEDIFF(second, LAGLastRunTime , isnull(LastRunTime, '20010101')) as delta_
FROM t

Error message:
Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Reason for the error:
The “guilty” is the first record – that don’t have LAG date – so the LAG value is ‘1900-01-01 00:00:00.000’.
And then –datediff can’t go with so big int value  - the seconds/minutes/etc. between 1900 to the first date – that is probably more than 100 years… J


Solution:
Convert the first date to some reasonable date:

; with t as
(      SELECT [ID],
                     [LastRunTime],
                     ( case
                           when LAG([LastRunTime], 1,0) OVER (ORDER BY [LastRunTime]) > '20010101'
                                  then LAG([LastRunTime], 1,0) OVER (ORDER BY [LastRunTime])
                                  else [LastRunTime] end
                     )  AS LAGLastRunTime
              , .....
       FROM .....
)
SELECT *, DATEDIFF(second, LAGLastRunTime , isnull(LastRunTime, '20010101')) as delta_
FROM t

No comments:

Post a Comment