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 |
|
M.E.
Aged Yak Warrior
539 Posts |
 Posted - 2002-05-22 : 11:54:19
|
This text file I'm importing has some really ugly datetimes like 1992051513434600. this should show as 1992-05-15 13:44:00. Now, convert(datetime,'1992051513434600') creates this error:Syntax error converting datetime from character string.Now if I format it into '19920515 13:43:46:00' and then convert that it has no problems at all. So I created something like this that auto formats it for me. (the @var would be replaced with values from a table) declare @var varchar(20)select @var = '1992051513434600'select convert(smalldatetime,((left(@var,8)) + ' ' + (left(right(@var,8),2)) + ':' + (left(right(@var,6),2))+':'+(left(right(@var,4),2))+':' +(right(@var,2)))) as 'new'Now this just looks bulky and kinda silly. Is there a better way to do this? bleh, it works... maybe I shouldn't be complaining. And sorry in advance if it's a rookie question  |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-22 : 12:05:08
|
| Not really better, but it might be more readable:SELECT convert(datetime, stuff(stuff(stuff(stuff(@var,15,0,'.'),13,0,':'),11,0,':'),9,0,' ')) |
 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-05-22 : 12:16:35
|
| Hmm, forgot about the stuff statement thnx.Another note, simular question I guess... and once again I went with rights and lefts6509894345this number needs to be broken down to943-50-45-98w6so its first numbers are the left(3,(right(5,@var)) +'-' + ....is there a better way of doing that one? or is the left/right thing my only option? I don't really see the stuff option working there |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-22 : 12:38:53
|
| SubString() would work:DECLARE @var varchar(20)SELECT @var='6509894345'SELECT SubString(@var, 6, 3) + '-' + SubString(@var, 2, 2) + '-' +Right(@var, 2) + '-' + SubString(@var, 4, 2) + 'w' + Left(@var, 1)I was gonna suggest with the date format that you tell whoever supplies the file to clean it up, and in this case I'm DEFINITELY gonna suggest it. That is completely ridiculous that you have to convert data like that. <rant>I don't know about you, but whenever someone tried crap like that with me I just outright refused, even though it's pretty simple to do, it can't be any more work for them to fix it than it is for me, and my time is at least as valuable as theirs. If you keep cleaning up stuff like that they'll just keep sending it to you.</rant> |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-05-22 : 12:49:44
|
| Lol, good rant good rant. Unfortunately part of the reason I'm here is to clean up an IDMS database and put in nice looking values such as this. So if I tell someone else to clean it up I have the strange feeling it will just circulate around until somebody figures out it's my job to clean it up and sends it back to me.<Rant> This goddamn IDMS database contains some 500 tables, none of them linked together nor containing keys that could link them together, 12 tables with over 500k lins and 4 of those over 2 million. Why they've let it grow this large I got no clue ... Now the tables can be changed in both mssql or through the third party proggy that lets them update the IDMS. So every night we need to sync the sql database and the IDMS database... thats about 30 million lines that needs to be compared... Worse yet, they're not paying me enough to do this... Although to be honest I first volunteered my time to do this job just for the experience... They said no, sorry we won't accept you as a volunteer because we can't accept the liability. 1 week after saying that they hired me instead. Silly</rant> |
|
|
|
|
|
|
|
|
Converting varchar to small date time