DATEADD function causing overflow

Question

I'm writing a StoredProcedure where I need to know whether there is a Resultset in the table that has been updated within the last 10 seconds. My idea was:

SELECT @CNT = ba_sales_funnel_id FROM CRM7.BA_SALES_FUNNEL WHERE project_id = @project_id
                                                            and sale_id = @sale_id
                                                            and DATEDIFF(SECOND, updated, GETDATE()) < 10

I get the following Error-Code here:

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.

Hope you can help me.

=======================================

Solution:

            SELECT @CNT = ba_sales_funnel_id FROM CRM7.BA_SALES_FUNNEL WHERE project_id = @project_id
                                                            and sale_id = @sale_id
                                                            and DATEDIFF(YEAR, updated, GETDATE()) < 1
                                                            and DATEDIFF(MONTH, updated, GETDATE()) < 1
                                                            and DATEDIFF(DAY, updated, GETDATE()) < 1
                                                            and DATEDIFF(SECOND, updated, GETDATE()) < 10

OR

SELECT @CNT = ba_sales_funnel_id FROM CRM7.BA_SALES_FUNNEL WHERE project_id = @project_id
                                                        and sale_id = @sale_id
                                                        and updated >= DATEADD(second,-10,GETDATE())

possible duplicate of [SQL Server: datediff function resulted in an overflow](http://stackoverflow.com/questions/1275208/sql-server-datediff-function-resulted-in-an-overflow) — LittleBobbyTables - Au Revoir, Sep 29 '14 at 15:06
Ty for your help. The problem is that the query steps through all rows in the table and checks the seconds only. Solution was the following: SELECT @CNT = ba_sales_funnel_id FROM CRM7.BA_SALES_FUNNEL WHERE project_id = project_id and sale_id = sale_id and DATEDIFF(YEAR, updated, GETDATE()) < 1 and DATEDIFF(MONTH, updated, GETDATE()) < 1 and DATEDIFF(DAY, updated, GETDATE()) < 1 and DATEDIFF(SECOND, updated, GETDATE()) < 10 — Sney, Sep 29 '14 at 15:43
Did you look at this answer in the duplicate? http://stackoverflow.com/a/23638804/334849 — LittleBobbyTables - Au Revoir, Sep 29 '14 at 15:44
I checked that before I posted the Question but it didn't work at first. I had to add month and day since I still had too many results. — Sney, Sep 29 '14 at 15:47

score 1 · Accepted Answer · answered Sep 29 '14 at 15:15

1

Try to change your SQL to this:

SELECT @CNT = ba_sales_funnel_id FROM CRM7.BA_SALES_FUNNEL WHERE project_id = @project_id
                                                            and sale_id = @sale_id
                                                            and updated >= DATEADD(second,-10,GETDATE())

answered Sep 29 '14 at 15:15

JanT

2,105
3
28
35

1

@Paul Yes, by running: select DATEDIFF(second, '1945-01-01', GETDATE()) – JanT Sep 29 '14 at 15:22
Thank you but that is no difference. Just an other way to write the statement. The problem seems to be that the query goes through ALL rows which causes an overflow. Answer is in the comments of first post. – Sney Sep 29 '14 at 15:40
@Sney I think you are wrong.. Overflow is very unlikely caused by number of rows.. It was difference between two dates that was too large to be displayed in seconds, as your error message does say.. Have you actually run my code? There is no way it can give overflow exception.. – JanT Sep 29 '14 at 15:47
1

Right, my mistake, I didn't notice you used DATEADD and not DATEDIFF. Your CODE works too! Sorry for that missunderstanding! – Sney Sep 29 '14 at 16:01

DATEADD function causing overflow

1 Answers1