regarding update statement - 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 2005 Forums

Transact-SQL (2005)

regarding update statement

Author

Topic

sarathaluri
Starting Member

6 Posts

Posted - 2007-10-16 : 14:04:12

hiiiiiiii

i am having a table with dob as one of the coloumn . but the dob format is mmddyyyy . i changed the format to yyyymmdd ...
by using the query
select right(dob,4)+left(dob,4)from tablename.
all the entries are changed .

and i need to update the table with this new values ......
waiting for the reply

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 14:47:00

You should use a DATETIME datatype column for dates.

Change

select right(dob,4)+left(dob,4)from tablename.

to


UPDATE U
SET dob = right(dob,4)+left(dob,4)
from tablename AS U

Backup first!!

Kristen

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-16 : 14:47:17

You are only changing the values when retrieving by SELECT. SELECT doesnt change the data in the table. You need to run an UPDATE to change the data in the table. But the bigger question is why are you messing with the data. Use the datetime datatype. Do the conversions for display purpose.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sarathaluri
Starting Member

6 Posts

Posted - 2007-10-16 : 18:54:14

U????
wat is this U?
Do u mean to say that u is a coloumn name or some thing

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-16 : 21:33:53

quote:
Originally posted by sarathaluri

U????
wat is this U?
Do u mean to say that u is a coloumn name or some thing

It is an alias. Read up books online.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-17 : 02:35:08

quote:
Originally posted by sarathaluri

hiiiiiiii

i am having a table with dob as one of the coloumn . but the dob format is mmddyyyy . i changed the format to yyyymmdd ...
by using the query
select right(dob,4)+left(dob,4)from tablename.
all the entries are changed .

and i need to update the table with this new values ......
waiting for the reply

Also make sure that the values are proper dates

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-17 : 02:37:28

quote:
Originally posted by Kristen

You should use a DATETIME datatype column for dates.

Change

select right(dob,4)+left(dob,4)from tablename.

to

UPDATE U
SET dob = right(dob,4)+left(dob,4)
from tablename AS U
where ISDATE(right(dob,4)+left(dob,4))=1 and len(right(dob,4)+left(dob,4))=8

Backup first!!

Kristen

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 03:51:52

Not sure that's a good idea Madhi - the OP won't know which ones have been swapped round, and which haven't ... Ah! Sorry, I see you cunning plan to introduce DATETIME datatype to the world!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-17 : 04:48:48

<<
I see you cunning plan to introduce DATETIME datatype to the world!
>>

Yes it is. I expect OP to create new column with proper DATETIME datatype and update values to that column

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources