trying to mod this script... need some help.

Question

trying to mod this script... need some help.

neotokyo

SSCertifiable

Points: 7239
More actions
May 18, 2006 at 7:47 am

#169518

thanks to a Veteran i have the following script
which will change db2's timestamp datatype to
a format which sql can understand and import.
-------------------------------------------------------
DECLARE @testDate AS DATETIME
DECLARE @stringDate AS VARCHAR(25)
SET @StringDate='2006-05-11-16.29.43.882000'
SET @StringDate=STUFF(STUFF(STUFF(LEFT(@StringDate,23),11,1 ,' '),14,1,':'),17,1,':')
PRINT @StringDate
SET @testDate=@StringDate
PRINT @testDate
--running this after import
UPDATE mytable SET mycolumn=STUFF(STUFF(STUFF(LEFT(mycolumn,23),11,1 ,' '),14,1,':'),17,1,':')
-------------------------------------------------------
the problem is that this is per one record only. suppose you have a
column with many different dates in it, and all of them need the
format adjusted in the same way.
can this be done?
_________________________

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 1

Perhaps I'm not understanding the question... but, if you execute the UPDATE statement without a WHERE clause, it will update mycolumn and reformat the date for all of the records in the table.

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Who says it's for one row only? Have you tried it?

This:

UPDATE mytable SET mycolumn=STUFF(STUFF(STUFF(LEFT(mycolumn,23),11,1 ,' '),14,1,':'),17,1,':')

should update the whole column.

-SQLBill

neotokyo SSCertifiable Points: 7239 More actions · Answer 3

juse so i understand...

so you are saying just this:

UPDATE mytable SET mycolumn=STUFF(STUFF(STUFF(LEFT(mycolumn,23),11,1 ,' '),14,1,':'),17,1,':')

is enough to set the entire columns worth of values to the proper

format AND still maintain the proper date of those records??

_________________________

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 4

Yes, because the mycolumn value inside of the STUFF function will change with each row. Why don't you test it as follows:

1) Run it on a copy of mytable

OR

Add a newcolumn to mytable and execute

UPDATE mytable SET newcolumn=STUFF(STUFF(STUFF(LEFT(mycolumn,23),11,1 ,' '),14,1,':'),17,1,':')

then you will see both the old & new values.

neotokyo SSCertifiable Points: 7239 More actions · Answer 5

oh ok... so all the declare's, set's, and print from the code set

just above it isn't needed at all?

thats great! i've overcomplicated this. don't know why i keep doing

that.

thanks!

_________________________