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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 392, Visits: 965
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: Tuesday, August 5, 2014 5:20 AM
Points: 3,546, Visits: 2,651
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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 392, Visits: 965
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: Yesterday @ 3:23 PM
Points: 13,272, Visits: 12,103
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