Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL query error Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 6:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 9:30 AM
Points: 403, Visits: 982
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
Posted Wednesday, March 6, 2013 7:08 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:40 AM
Points: 3,546, Visits: 2,652
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
Posted Wednesday, March 6, 2013 7:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 9:30 AM
Points: 403, Visits: 982
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
Posted Wednesday, March 6, 2013 7:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 13,481, Visits: 12,342
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1427397
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse