Substring w/ update - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Substring w/ update

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-27 : 09:35:05

Gabriel writes "I want to update just a portion of a field, specifically the month of a YYYYMMDD field

I can select with a substring like the following to identify records that need fixing:

(Finds dates with a desc of JAN, but month <> 01)

select * from date_table
where SUBSTRING(isu_dsc,4,4) = '/JAN'
and SUBSTRING(isu_dte,5,2) <> 01

RESULTS

ISU_DTE         ISU_DSC
--------        --------
19861201	DEC/JAN
19871201	DEC/JAN
19881201	DEC/JAN
19891201	DEC/JAN
19901201	DEC/JAN

How can I set the month portion of the date field to 01 ?

Thanks for your help ..."

dsdeming

479 Posts

Posted - 2002-06-27 : 09:41:39

Try this:

UPDATE date_table
SET isu_dte = LEFT( isu_dte, 4 ) + '01' + RIGHT( isu_dte, 2 )
WHERE SUBSTRING(isu_dsc,4,4) = '/JAN'
and SUBSTRING(isu_dte,5,2) <> 01

HTH

MakeYourDaddyProud

184 Posts

Posted - 2002-06-27 : 09:49:20

UPDATE date_table
SET ISU_DTE = left(ISU_DTE,4) + '01' + rtrim(right(ISU_DTE,2))
WHERE SUBSTRING(isu_dsc,4,4) = '/JAN'
AND SUBSTRING(isu_dte,5,2) <> 01

and repeat that procedure for each of the other months in error

Daniel Small MIAP
www.danielsmall.com IT Factoring

MakeYourDaddyProud

184 Posts

Posted - 2002-06-27 : 09:50:36

Coincidence! I think u need the rtrim to lose any trailing spaces u might have....

Your thoughts

Daniel Small MIAP
www.danielsmall.com IT Factoring

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources