Apr 28, 2004 #1 JohnBates MIS Joined Feb 27, 2000 Messages 1,995 Location US hi, This char date is like 20040214 with no dashes, slashes. If I can insert dashes to get 2004-02-14 then it will convert to a DateTime data type in the DTS transformation task. Just need a little tip on how to insert the dashes with code. Thanks, John
hi, This char date is like 20040214 with no dashes, slashes. If I can insert dashes to get 2004-02-14 then it will convert to a DateTime data type in the DTS transformation task. Just need a little tip on how to insert the dashes with code. Thanks, John
Apr 28, 2004 Thread starter #2 JohnBates MIS Joined Feb 27, 2000 Messages 1,995 Location US ... and I should have explained that I actually want to update the row with the new char value of 2004-02-14 for example. John Upvote 0 Downvote
... and I should have explained that I actually want to update the row with the new char value of 2004-02-14 for example. John
Apr 29, 2004 #3 dbomrrsm Programmer Joined Feb 20, 2004 Messages 1,709 Location GB Update table SET yourfield = LEFT(Yourfield,4)+-+SUBSTRING(Yourfield,5,2)+-+Right(Yourfield,2) You could use substring in all three occasions above but thought would put in left and right to show their use. Substring explained is the field you are interested in the start position of what you want and the length of what you want. Hope this helps. DBomrrsm Upvote 0 Downvote
Update table SET yourfield = LEFT(Yourfield,4)+-+SUBSTRING(Yourfield,5,2)+-+Right(Yourfield,2) You could use substring in all three occasions above but thought would put in left and right to show their use. Substring explained is the field you are interested in the start position of what you want and the length of what you want. Hope this helps. DBomrrsm
Apr 29, 2004 #4 Connatic Programmer Joined Apr 22, 2004 Messages 45 Location GB Or you could use this: Code: declare @Date as CHAR(10) SET @Date = CONVERT(CHAR(10),CONVERT(DATETIME,'20040214'),105) PRINT @Date The above example will show you how it works, but you will want it to look like Code: SET yourfield = CONVERT(CHAR(10),CONVERT(DATETIME,yourfield),105) Upvote 0 Downvote
Or you could use this: Code: declare @Date as CHAR(10) SET @Date = CONVERT(CHAR(10),CONVERT(DATETIME,'20040214'),105) PRINT @Date The above example will show you how it works, but you will want it to look like Code: SET yourfield = CONVERT(CHAR(10),CONVERT(DATETIME,yourfield),105)