0

I'm trying to add 13 years to a birthdate that's stored as datetime2 using the following syntax and (dateadd(yy,13,isNull(cast(p.birthDTS as datetime),cast('1/1/1950' as datetime)))>=@svcStart)

and I get the Adding a value to a 'datetime' column caused an overflow.

I searched for the max(p.birthDTS) and it returned 1988-10-17 00:00:00.0000000

I got the for @svcStart as 2015-04-08 13:10:49.193

I'm a bit flummoxed. Any help?

jb

user1795131
  • 47
  • 4

2 Answers2

1

If you are working with values that are DATETIME2 you may want to try casting your values to DATETIME2 as opposed to DATETIME :

DATEADD(yy,13,ISNULL(CAST(p.birthDTS AS DATETIME2),CAST('1/1/1950' AS DATETIME2)))

Assuming you don't have any invalid data or a similar issue and the example data that you provided is correct (i.e. the largest is 1988-10-17), which seems correct as seen here :

enter image description here

I'd make sure that you double check all of your data types to ensure they are defined as expected and consider querying through your data again to check any edge-cases (e.g. largest and smallest values, possible nulls, etc.).

Rion Williams
  • 74,820
  • 37
  • 200
  • 327
0

You might face this error if the value in your records in the datefield is not a valid date or if u add some months or date to this value and the output of that will exceed the date range.

GabrielVa
  • 2,353
  • 9
  • 37
  • 59
  • You were right. The max birthdate in the table is obviously an error at 9999-01-01 00:00:00.0000000, but I'm going to need to check for it to exclude the error. Thanks so much for your comment. – user1795131 Apr 08 '16 at 20:01
  • @user1795131 So when you said, "I searched for the max(p.birthDTS) and it returned 1988-10-17 00:00:00.0000000"..? – Matt Gibson Apr 08 '16 at 20:52