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 — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Sep 29, 2014 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
– Sney, Commented Sep 29, 2014 at 15:43
Did you look at this answer in the duplicate? stackoverflow.com/a/23638804/334849 — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Sep 29, 2014 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
– Sney, Commented Sep 29, 2014 at 15:47

JanT · Accepted Answer · 2014-09-29 15:15:33Z

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, 2014 at 15:15

JanT

2,0963 gold badges30 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JanT Over a year ago

@Paul Yes, by running: select DATEDIFF(second, '1945-01-01', GETDATE())

Sney Over a year ago

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.

JanT Over a year ago

@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..

Sney Over a year ago

Right, my mistake, I didn't notice you used DATEADD and not DATEDIFF. Your CODE works too! Sorry for that missunderstanding!

Collectives™ on Stack Overflow

DATEADD function causing overflow

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related