Alter Data and Re-Insert - 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

General SQL Server Forums

New to SQL Server Programming

Alter Data and Re-Insert

Author

Topic

MevaD
Starting Member

32 Posts

Posted - 2008-10-23 : 12:01:18

Hi,

I need to alter data from one column of a table and insert it into another column in the same table. I'm using MS-SQL 2005 as the DB.

Here is what the table looks like:

Table1
------------
id
fName
lName
dateOfBirth

The dateOfBirth data is imported from another source in the format ddmmmyy as pure text so I can't use date functions to convert.

It looks like this:

31Jan99
18Apr07
12Dec68

Is it possible to convert to:

990131
070418
681212

and then insert the results back into the same table?

Thank you.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 12:38:29

select replace(convert(varchar(8),cast('31JAN99' as datetime),11),'/','')

MevaD
Starting Member

32 Posts

Posted - 2008-10-23 : 12:50:09

I tried the following:

select replace(convert(varchar(8),cast(dateOfBirth as datetime),11),'/','') from Table1

Getting the following error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

Any ideas?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 13:03:40

One of your dateOfBirth values isn't in the correct date format.

show me some :
select * from tabl1 where isdate(dateOfBirth)=0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 13:04:22

make a datetime field and store into it like this

SELECT CONVERT(datetime,STUFF(STUFF(dateOfBirth,3,0,' '),7,0,' '),6) from table1

MevaD
Starting Member

32 Posts

Posted - 2008-10-23 : 13:11:23

Thanks hanbingl.

I ran select * from tabl1 where isdate(dateOfBirth)=0

The results are 377 rows of blank data and 5 rows with a value of 0

This data comes from another source so I don't have much control over what is in the column.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 13:13:32

juse filter out your query:


select replace(convert(varchar(8),cast(dateOfBirth as datetime),11),'/','') from Table1
where isdate(dateOfBirth )=1

The question is how do you want to deal with blanks and 0s...

MevaD
Starting Member

32 Posts

Posted - 2008-10-23 : 13:22:00

I'll probably just dump them out as errors with the front-end application.

Thanks hanbingl and visakh!!!

I really appreciate your help.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 13:24:30

YOU BETCHA!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources