SQL query error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL query error

SQL query error

Rate Topic

Display Mode

Topic Options

Author

Message

balasach82

Posted Wednesday, March 06, 2013 6:58 AM

Old Hand

Group: General Forum Members
Last Login: Monday, June 10, 2013 6:23 PM
Points: 311, Visits: 757

select cast(COL1 as datetime) from TABLE where COL1<>'' and isdate(COL1) =1 and
cast(COL1 as datetime) < (DATEADD(YY, -3, GETDATE()))

above query gives the error
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

COL1 is an nvarchar column

Any idea why?

Post #1427363

sqlnaive

Posted Wednesday, March 06, 2013 7:08 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 2,797, Visits: 1,746

Definitely something to do with the kind of data you have inside the table. Could you provide some of the data inside the table Table for COL1 ?

Post #1427372

balasach82

Posted Wednesday, March 06, 2013 7:27 AM

Old Hand

Group: General Forum Members
Last Login: Monday, June 10, 2013 6:23 PM
Points: 311, Visits: 757

08/17/2009 in mm/dd/yyyy format. If you see the query, i filter out invalid dates, which are nulls, blanks etc

Post #1427391

Sean Lange

Posted Wednesday, March 06, 2013 7:34 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 8,980, Visits: 8,540

You have something in there that can't be cast as a datetime and it is failing. Your where clause also casts Col1 as a datetime so if there is something in the table it will throw an exception.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1427397

« Prev Topic | Next Topic »

Permissions