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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-09 : 22:46:56
|
spins writes "I need to update two columns of data in a pre-determined format.
The sp looks like this:
CREATE PROCEDURE Upload_To_Convert AS
create table convert3 (EUDATE varchar (11) , EUTIME varchar(8))
insert into convert3 ( EUDATE, EUTIME)
select EUDATE, convert(varchar, EUTIME, 8) from Uploaded_Data
create table convert2 (EUYEAR varchar (4), EUMONTH varchar(2), EUDAY varchar(2))
insert into convert2 (EUYEAR, EUMONTH, EUDAY)
select datepart(yyyy, EUDATE), datepart(mm, EUDATE), datepart(dd, EUDATE)
from convert3
Update convert2 set EUMONTH =
CASE
WHEN EUMONTH = '1' THEN '01'
WHEN EUMONTH = '2' THEN '02'
WHEN EUMONTH = '3' THEN '03'
WHEN EUMONTH = '4' THEN '04'
WHEN EUMONTH = '5' THEN '05'
WHEN EUMONTH = '6' THEN '06'
WHEN EUMONTH = '7' THEN '07'
WHEN EUMONTH = '8' THEN '08'
WHEN EUMONTH = '9' THEN '09'
WHEN EUMONTH = '10' THEN '10'
WHEN EUMONTH = '11' THEN '11'
WHEN EUMONTH = '12' THEN '12'
ELSE NULL
END
from convert2
Update convert3 set EUDATE = replace(EUDATE, EUDATE, convert2.EUYEAR+EUMONTH+EUDAY) from convert2
Update convert3 set EUTIME = replace(EUTIME, ':', '')
from convert3
The result should have tw columns with the date as eg. 20010228 and the time as eg. 060000.
The actual result has the correct times however the date appears as a single date down the column with no change.
eg.
20010327 091500
20010327 082000
20010327 153000
20010327 135000
20010327 093000
20010327 074500
20010327 122000
20010327 133000
20010327 001800
20010327 090000
20010327 114500
can anyone help?" |
|
|
|
|
|
|
|
Updating columns from other tables