Arithmetic overflow error converting expression to

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

General SQL Server Forums

New to SQL Server Programming

Arithmetic overflow error converting expression to

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-09 : 22:17:04

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.

I get the above error when I try to execute the following SQL

select [property], [address], city, [state], zip, country,
inday = case when checkindate like '%day%' then left(checkindate, charindex(',', checkindate) - 1) else checkindate end,
checkindate = case when checkindate like '%day%' then convert(datetime, (reverse(left( reverse(checkindate), charindex('y', reverse(checkindate))-3))), 100) else checkindate end,
outday = case when checkoutdate like '%day%' then left(checkoutdate, charindex(',', checkoutdate) - 1) else checkoutdate end,
checkoutdate = case when checkoutdate like '%day%' then convert(datetime, (reverse(left( reverse(checkoutdate), charindex('y', reverse(checkoutdate))-3))), 100) else checkoutdate end,
roomtype,
roomrate = case when roomrate like '%usd%' then replace(left(roomrate, charindex('.',roomrate)-1), ',', '')
when roomrate like '%sold%' then 'Sold Out'
when roomrate = '' then 'sold out'
else roomrate end,
inputparam, agentname,
dateadd(dd, datediff(dd, 0, executiontime), 0) executiontime, id_num

from marriott
where id_num = '1378445'

The entry looks like this

Property Address City State Zip Country CheckInDate CheckOutDate RoomType RoomRate InputParam AgentName ExecutionTime id_num
Courtyard Dallas Mesquite 2300 Interstate 30 Mesquite TX 75150 USA Wednesday, January 7, 2009 Thursday, January 8, 2009 Guest room, 2 Double, No view 139.95(USD) Dallas_TX_US Marriott - Four Weeks Wednesday 12/7/2008 1:00:01 PM 1378445

What I'm basically trying to is change the days "wednesday, january 7, 2009" and "thursday, January 8, 2009" into its proper Datetime of '1/7/2009' and '1/9/2009' etc

Everything was working fine until the new year changed over. Not sure how to address this

The datatype of the columns with the dates in them are Nvarchar(500)

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-12-10 : 01:46:38

change the columns data type to datetime.

Arnav
Even you learn 1%, Learn it with 100% confidence.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 02:12:08

Wednesday, January 7, 2009 is not a valid date value. see this

SELECT ISDATE('Wednesday, January 7, 2009')

so convert the values to valid date values before turning them to datetime

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-10 : 18:30:37

quote:
Originally posted by visakh16

Wednesday, January 7, 2009 is not a valid date value. see this

SELECT ISDATE('Wednesday, January 7, 2009')

so convert the values to valid date values before turning them to datetime

don't these regex type functions doing that part?

checkindate = case when checkindate like '%day%' then convert(datetime, (reverse(left( reverse(checkindate), charindex('y', reverse(checkindate))-3))), 100) else checkindate end,

checkoutdate = case when checkoutdate like '%day%' then convert(datetime, (reverse(left( reverse(checkoutdate), charindex('y', reverse(checkoutdate))-3))), 100) else checkoutdate end,

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-10 : 18:45:21

Wednesday, January 7, 2009

I figured out the problem.

checkindate = case when checkindate like '%day%' then convert(datetime, (reverse(left( reverse(checkindate), charindex('y', reverse(checkindate))-3))), 100) else checkindate end,

looks for the 'y' in the day and it removes the day. However there is also a y in january, so its fucking the conversion. Is there any other way to turn

Wednesday, January 7, 2009

or

Wednesday, December 7, 2009

into January 7, 2009 and December 7, 2009 without using that 'y' ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 20:56:55

yup. just do

CONVERT(datetime,LEFT(checkindate,charindex(',',checkindate)-1))

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-10 : 21:10:39

quote:
Originally posted by visakh16

yup. just do

CONVERT(datetime,LEFT(checkindate,charindex(',',checkindate)-1))

I tried to do this on columns that are formatted like this

CheckInDate CheckOutDate
Wednesday, January 7, 2009 Thursday, January 8, 2009
Saturday, December 20, 2008 Sunday, December 21, 2008

and it came up with

Msg 241, Level 16, State 1, Line 19
Conversion failed when converting datetime from character string.

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-10 : 21:14:21

I removed the convert portion, and it resulted in only the day

eg. Wednesday, Thursday

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 21:41:30

[code]CONVERT(datetime,SUBSTRING(checkindate,charindex(',',checkindate)+1,LEN(checkindate)))[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources