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 |
|
Sayanora
Starting Member
1 Post |
 Posted - 2010-04-28 : 15:22:45
|
| Hiii ...I am new to this sql programming, can anyone please help me to out the solution for my following error:I am having two tables in my database and i want to transfer the some data from one tabel to another using select query. But the column name with 'SubmissionDate' having nvarchar(50) in one table and datetime in another table . So that i need to use cast operator in the sql query but its seems to be not working:The query i used is the folllowing:INSERT INTO [Mr_site].[dbo].[MST_SUBMISSION] ([UniqueNo],[EmailId],[Title],[ResearchType],[Consent],[StudyDesign],[StudyArea],[BudgetType],[RequestedAmount],[SanctionedAmount],[ExternalFunding],[Status],[StatusDate],[ApprovalDate],[SubmissionStatus],[SubmissionDate])SELECT RPNo, Email1, Title,ResearchType,Consent,StudyDesign, StudyArea, BudgetType,RequestedAmount,SanctionedAmount, ExternalFunding, CurrentStatus, StatusDate, ApprovalDate,CurrentStatus,Cast (SubmissionDate as datetime) FROM [Mr_site].[dbo].[TABLE_OLDDB]i got the following error by executing the above:Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.the data i am having in the submission date column in my OLDDB table is like in the following format:5/7/2009 12:005/8/2009 12:0012/4/1997 12:0029/10/2002 12:00:0017/11/2001 12:00:001/8/2000 12:00....like this way i have more than 1050 data in my table.Can any one help me to fix this problem. I am urgently in need of helpI am using sql server 2005 express edition |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-04-28 : 15:27:47
|
| You could try using the SET DATEFORMAT option.. Like:SET DATEFORMAT DMY;-- Run query |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-30 : 02:14:19
|
| try converting the nvarchar field date values to datetime typelike belowSELECT CONVERT(datetime,[submissiondate ],103),...------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-04-30 : 03:53:39
|
| Always use proper DATETIME datatype to store datesMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Cast Operator Error