| Author |
Topic |
|
allend2010
Starting Member
28 Posts |
 Posted - 2003-03-28 : 08:37:59
|
| Hello:I have a long varchar field that I am passing into a stored procedure and I am attempting to convert a portion of it into a datetime field,however I keep getting an error with it. the part of the field I am working with looks like this:DECLARE @FileData VARCHAR(85)SET @FileData = 'S,02102003000000,1,00,1,00544'DECLARE @Date DATETIME--Field is in the current format of '02102003'SET @Date = SUBSTRING(@FileData, 3, 2) + '/' + SUBSTRING(@FileData, 5, 2) + '/'+ SUBSTRING(@FileData, 7, 4)...Also, just for fun, I tried :SET @Date = SUBSTRING(@FileData, 3, 8) andSET @Date = CONVERT(DATETIME, SUBSTRING(@FileData, 3, 8))When I attempt to execute this however I keep getting an error:The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Any help anybody could provide would be greatly appreciated. Thanks,Allen D. |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-03-28 : 08:49:18
|
| Check Here for Arnonld Explaination on Date Time format.[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=24397[/url]select Convert(DateTime,'YYYYMMDD')select Convert(DateTime,'20030210')Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
 |
|
|
allend2010
Starting Member
28 Posts |
Posted - 2003-03-28 : 09:15:38
|
| Thanks, using the '20030210' format worked for me :) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-28 : 10:08:09
|
| Don't know why your original parse didn't work.All but @y will Convert:Declare @w varchar(255),@x varchar(255), @y varchar(255), @z varchar(255)Select @w = '20030101', @x='01/01/2003', @y='01012003', @z = Substring(@y,1,2)+'/'+Substring(@y,3,2)+'/'+Substring(@y,5,4)Select Convert(datetime,@w,103),Convert(datetime,@x,103),Convert(datetime,@z,103)Happy datingBrett8-) |
|
|
|
|
|
|
Converting part of a varchar field to datetime