• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

need to convert string 030308 into smalldatetime

when using the below query I get the error 'Arithmetic overflow error converting expression to data type smalldatetime.'

UPDATE    tblCourseHistory
SET              Starting_Date = RIGHT(Course_Code, 6)
WHERE     (Starting_Date IS NULL)

Starting_Date has a datatype of smalldatetime
Course_Code has a dataype of nvarchar
0
NevSoFly
Asked:
NevSoFly
  • 2
1 Solution
 
ViceroyFizzlebottomCommented:
Try:

Starting_Date = CAST(RIGHT(Course_Code,6) AS SMALLDATETIME)
0
 
ViceroyFizzlebottomCommented:
The other thing that may be going on is if Course_Code does not have at leas 6 characters. You may want to check your string length before making the assignment.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this format will not "work" directly for cast/convert:
http://msdn.microsoft.com/en-us/library/ms187928.aspx

so, a bit of manual work will do:
UPDATE    tblCourseHistory
SET              Starting_Date = CONVERT(datetime,   RIGHT(Course_Code, 2)  + LEFT(RIGHT(Course_Code, 6), 4),   12)
WHERE     (Starting_Date IS NULL)

Open in new window

0
 
NevSoFlyAuthor Commented:
Thank you
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now