nvarchar to datetime conversion plz!!!!!!!!!!!!!!!!!!!!!!!!!!
-
hi everybody i have two tables table1 with column name SubmissionDate nvarchar(11) table2 with column name submissiondate2 (datetime) and i insert data in table1 like 17/05/2009 but still in nvarchar format cos this is my requirement (i mean nvarchar) my question is i want to insert same date from table1's column to table2's column but dont know how to convert datatype nvarchar to datetime. when i try to cast i got this error msg Arithmetic overflow error converting expression to data type datetime. any help would be really appricated
-
hi everybody i have two tables table1 with column name SubmissionDate nvarchar(11) table2 with column name submissiondate2 (datetime) and i insert data in table1 like 17/05/2009 but still in nvarchar format cos this is my requirement (i mean nvarchar) my question is i want to insert same date from table1's column to table2's column but dont know how to convert datatype nvarchar to datetime. when i try to cast i got this error msg Arithmetic overflow error converting expression to data type datetime. any help would be really appricated
http://msdn.microsoft.com/en-us/library/ms187928.aspx[^]
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
-
hi everybody i have two tables table1 with column name SubmissionDate nvarchar(11) table2 with column name submissiondate2 (datetime) and i insert data in table1 like 17/05/2009 but still in nvarchar format cos this is my requirement (i mean nvarchar) my question is i want to insert same date from table1's column to table2's column but dont know how to convert datatype nvarchar to datetime. when i try to cast i got this error msg Arithmetic overflow error converting expression to data type datetime. any help would be really appricated
-
hi everybody i have two tables table1 with column name SubmissionDate nvarchar(11) table2 with column name submissiondate2 (datetime) and i insert data in table1 like 17/05/2009 but still in nvarchar format cos this is my requirement (i mean nvarchar) my question is i want to insert same date from table1's column to table2's column but dont know how to convert datatype nvarchar to datetime. when i try to cast i got this error msg Arithmetic overflow error converting expression to data type datetime. any help would be really appricated
This is a fundamental error that a lot of newbies make - storing dates as strings. Your dates are stored in a format other than yyyy-mm-dd, probably dd/mm/yyyy and SQL cannot decide between the dd and mm structures, therefore the error. Try using
Convert(datetime,datefield,106)or one of the other formatters. Under convert/cast in BOL.Never underestimate the power of human stupidity RAH
-
This is a fundamental error that a lot of newbies make - storing dates as strings. Your dates are stored in a format other than yyyy-mm-dd, probably dd/mm/yyyy and SQL cannot decide between the dd and mm structures, therefore the error. Try using
Convert(datetime,datefield,106)or one of the other formatters. Under convert/cast in BOL.Never underestimate the power of human stupidity RAH
thanks holmes for your kind answer my Problem; the following code is working perfectly except date. every thing is working perfectly pleas let me know how to add Date into table through following query, and dont worry about other variables these are working perfectly because i have changed them into varchar like this SELECT @str_Session_Id=CONVERT(nvarchar(10), CONVERT(int, @SessionId)) but i dont want to add date like varchar because i have to compare date with other dates therefore i dont want to convert into varchar. select @date =(select DATEADD(dd, 1, GETDATE())) Begin set @txtSql = case when (@Std_Count < 3) then 'insert into ProjectRequests values('+ @str_stu_Id+','+ @str_Priority_Id +','+ @str_Project_Id +' , '+ @str_Session_Id +', '+ @date +' , NULL)' Msg 241, Level 16, State 1, Procedure FUALLY_QUALIFIED_STUDENT_BULK, Line 48 Conversion failed when converting datetime from character string. any help would be appreciated.
-
thanks holmes for your kind answer my Problem; the following code is working perfectly except date. every thing is working perfectly pleas let me know how to add Date into table through following query, and dont worry about other variables these are working perfectly because i have changed them into varchar like this SELECT @str_Session_Id=CONVERT(nvarchar(10), CONVERT(int, @SessionId)) but i dont want to add date like varchar because i have to compare date with other dates therefore i dont want to convert into varchar. select @date =(select DATEADD(dd, 1, GETDATE())) Begin set @txtSql = case when (@Std_Count < 3) then 'insert into ProjectRequests values('+ @str_stu_Id+','+ @str_Priority_Id +','+ @str_Project_Id +' , '+ @str_Session_Id +', '+ @date +' , NULL)' Msg 241, Level 16, State 1, Procedure FUALLY_QUALIFIED_STUDENT_BULK, Line 48 Conversion failed when converting datetime from character string. any help would be appreciated.
Do some research into parameterised queries, they will make your life so much easier. Inserting date using a string var is Ok, you just need to format it correctly, the following are valid formats for SQL Server. The important thing is to STORE it as a datetime data type in the database. yyyy/mm/dd dd/MMM/yyyy mm/dd/yyyy If you try to use a date in dd/mm/yyyy format it will create the error you are getting. MS is a US company so SQL Server used the stupid freakin US date format internally. Also you need to add additional ' around your date value.
Never underestimate the power of human stupidity RAH