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.
| Author |
Topic |
|
shiyam198
Yak Posting Veteran
94 Posts |
 Posted - 2010-04-08 : 17:11:38
|
| Hi,I am trying this command - select src_performance_start_date, series_data2from AspenShowData01.dbo.src_performance_ga inner join AspenShowTest02.dbo.ts_serieson series_data1 = src_performance_nameand series_data2 = src_performance_start_dateand getting this error.Arithmetic overflow error converting expression to data type datetime.which is related to the part "and series_data2 = src_performance_start_date"AspenShowTest02.dbo.ts_series - series_data2 is NVARCHAR(80)AspenShowData01.dbo.src_performance_ga - src_performance_start_date is "DateTime" format.Here is the screenshot of select series_data2 from AspenShowTest02.dbo.ts_serieshttp://img535.imageshack.us/img535/4492/seriesdata2.pnghere is the screenshot of select src_performance_start_date from AspenShowData01.dbo.src_performance_gahttp://img297.imageshack.us/img297/5818/startdate.pngBizarre thing is this is working fine in another server. Not sure what is going on here. Any help is appreciated.Thanks in advance,Shiyam |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-04-08 : 18:23:43
|
| you need to convert the column to datetime before you join the columns. It might seem to work because SQL Server was able to implicitly convert the values you have into datetime. If your application does not have proper validations in place, soon someone will enter non-date values into the column and you will see more and more of these errors. A cleaner solution is to convert the column into datetime type to avoid these conversion issues.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-08 : 18:48:13
|
quote:
Originally posted by dinakar
you need to convert the column to datetime before you join the columns.
That's not it. The cast will return the same error. The problem is the date format setting. The source table is expecting a format setting of dmy. Put the following line before the query, it will then run correctly. SET DATEFORMAT DMY This is another good example of why you should use DATETIME types to store dates.There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-04-08 : 20:20:54
|
| If the data going into the column is not validated, the SET option will still return an error. Thats what I meant by converting the column to datetime. Modify the datatype of the column to datetime from nvarchar as it is currently.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
shiyam198
Yak Posting Veteran
94 Posts |
Posted - 2010-04-08 : 22:09:00
|
| Thanks Guys for your time."SET DATEFORMAT DMY" did the trick.Just an FYI - doing "CAST" and "CONVERT" still generated the error. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-04-08 : 22:46:38
|
quote:
Originally posted by shiyam198
Thanks Guys for your time."SET DATEFORMAT DMY" did the trick.Just an FYI - doing "CAST" and "CONVERT" still generated the error.
How does your convert() looks like ? Did you specify the correct style ?refer to CAST and CONVERT (Transact-SQL)
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
Dateformat
