Adding a value to a 'datetime' column caused overf

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Adding a value to a 'datetime' column caused overf

Author

Topic

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-16 : 03:50:29

Dear Member
i use following query in table to subtract days in date column
select dateadd(day,-20, dat) from tab1.
but their is one error
Adding a value to a 'datetime' column caused overflow.
so what is error or how can write this query

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-16 : 04:35:12

what is the datatype of dat?

Madhivanan

Failing to plan is Planning to fail

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-16 : 04:37:12

it is datetime

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-16 : 04:40:03

What is the result of this?

select max(dat) from tab1

Madhivanan

Failing to plan is Planning to fail

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-16 : 04:56:36

it is display to right date

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-16 : 05:11:09

What madhivanan is trying to establish is for example if you are using SMALLDATETIME and your dat column datetimevalue is very near the lower boundary of the SMALLDATETIME value range.
If you try to deduct 20 days from '19000110' you will get the error as described.

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-16 : 05:12:13

quote:
Originally posted by amirs

it is display to right date

Post the result

Madhivanan

Failing to plan is Planning to fail

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-16 : 05:26:15

Dear Peso/madhivanan

i have try to deduct -1 value in date but it is same error message
it is only work to add days not deduct.
so please tell me another way to deduct maximum 500 days in date colomn

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-16 : 09:14:08

Not possible without seeing the result of this

select min(dat) from tab1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources