Adding Days to where data range is int

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 Days to where data range is int

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-04-19 : 16:38:36

Good day, i have an INT column where i want specify addiotnal date range.

I manage to get it to work on getdate but not on the column itself - i tried converting but i get "arithmetic overflow"

and PHCal.TrxDateNumeric >= convert(varchar,DATEADD(day,-3,Getdate()), 112)

but when i try to convert the column
and PHCal.TrxDateNumeric between convert(varchar,DATEADD(day,+1,cast(PHCal.TrxDateNumeric as datetime)), 112) and convert(varchar,DATEADD(day,+3,cast(PHCal.TrxDateNumeric as datetime)), 112)

I get the error, pleae help - the datatype is INT

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-19 : 16:46:05

The error means you have to data that won't fit into an INT data type. Perhaps you need BIGINT instead.

What are you trying to do and why aren't you using datetime data type instead?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-04-19 : 17:20:09

Thanks for helping, my col1 (int)

I would like to range 14 days in to the future

where col1 between col2 and col1+14 days

when i do this it works using Getdate(): col1 between convert(varchar,DATEADD(day,0,Getdate()), 112) and convert(varchar,DATEADD(day,+14,Getdate()), 112)

but not if i change the Getdate to the Col1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-19 : 17:23:25

What does this show:?

SELECT MAX(col1) FROM YourTable

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-04-19 : 17:37:32

20100418

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-19 : 17:55:28

Well that's not it then. Have you tried adding a size for varchar?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-04-19 : 18:03:13

yes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-19 : 18:06:22

"where col1 between col2 and col1+14 days"

Is col2 ever greater than col1+14?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-04-19 : 18:13:22

yes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-19 : 18:46:47

I was going down the wrong path. Your issue is due to comparing ints and datetimes.

where col1 between col2 and CONVERT(char(8), DATEADD(day, 14, CONVERT(varchar(50), col1)), 112)

You could add an outer CONVERT to INT, but an implicit conversion is happening for you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-04-20 : 03:04:16

Thank You very much, i appreciate your time & kindness. :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-20 : 12:06:20

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources