editing each string in a column

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

General SQL Server Forums

New to SQL Server Programming

editing each string in a column

Author

Topic

qgofil
Starting Member

1 Post

Posted - 2011-12-25 : 21:10:00

Hello All,

I have an sql table with a column that is a varchar. It stores a string to represent a date. It is dd/mm/yyyy. I need to export this data (there are MANY records) to a MySQL database whose column is date (YYYY-MM-DD). I am attempting to format this data before making flat file (wind up with NULL on import if I do not). Can anyone spot why my SQL code is not updating the column values in my SQL table? Any help will be much appreciated.

------------------------------------------

DECLARE @ID varchar(10)
DECLARE c1 CURSOR FOR
SELECT bdate
FROM test_query_results
FOR UPDATE OF bdate

OPEN c1
FETCH NEXT FROM c1
INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @firstslash AS INT SELECT CHARINDEX('/',@ID,0)
DECLARE @secondslash AS INT SELECT CHARINDEX('/',@ID,3)
DECLARE @strlength AS INT SELECT LEN(@ID)

DECLARE @day char(2) SELECT LEFT(@ID,@firstslash - 1)
DECLARE @month char(2) SELECT SUBSTRING(@id,@firstslash + 1, @strlength - 5)
DECLARE @year char(4) SELECT RIGHT(@ID,@secondslash + 1)
DECLARE @formatted char(12) SELECT (@year + '-' + @month + '-' + @day)

UPDATE test_query_results
SET @ID = @formatted
WHERE CURRENT OF c1

FETCH NEXT FROM c1
INTO @ID
END
CLOSE c1
DEALLOCATE c1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-25 : 23:41:59

why do you need a cursor for this?

isnt below enough?


--example table with varchar dates in format dd/mm/yyyy
declare @tbl table
(
datevar varchar(20)
)

insert @tbl
select '23/12/2010' union all
select '12/05/2011' union all
select '19/02/2010'

select datevar, CONVERT(datetime,datevar,105) as dateequivalent
from @tbl


output
----------------------------------
datevar	        dateequivalent
----------------------------------
23/12/2010	2010-12-23 00:00:00.000
12/05/2011	2011-05-12 00:00:00.000
19/02/2010	2010-02-19 00:00:00.000

also two things to remember always

1. always try to use proper datatype for your fields. storing date values in varchar fields makes date manipulation difficult.
2. always try to pass date in unambiguos formats like YYYY-MM-DD,YYYYMMDD etc

see this to understand why

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources