Update Query - 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)

Update Query

Author

Topic

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-09-15 : 00:07:13

I have a column with entries like 13-Sept-1980.
I will like to change it to 13/09/1980.

How do I do so?

When Jan = 01, Feb = 02...etc
Also, changing the "-" to "/".

- HELP -

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-15 : 00:44:50

Assuming @c is your date column:

UPDATE MyTable 
SET myDateField = LEFT(@c, 2) + '/' + 
Case Substring(myDateField, 4,4) 
  WHEN 'Jan' THEN '01' 
  .. other cases here ....
  WHEN 'Sept' THEN '09' 
END + '/' + SubString(myDateField, 9, 4)

You could use SQL Server's date functions if you were using 3-character names for your dates.....

UPDATE MyTable 
SET myDateField = convert(varchar, convert(datetime, @c), 103)

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-09-15 : 00:54:37

Thank You!

myDateField = column name?

@c = ...?

I am a bit confuse. Sorry.

- HELP -

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-15 : 01:19:00

Sorry - I was testing using @c parameter. Just switch with your column name

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-09-15 : 01:55:08

So I do this?

UPDATE MyTable
SET columnName = LEFT(columnName, 2) + '/' +
Case Substring(columnName, 4,4)
WHEN 'Jan' THEN '01'
.. other cases here ....
WHEN 'Sept' THEN '09'
END + '/' + SubString(columnName, 9, 4)

- HELP -

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-15 : 01:59:41

No, you'll need to adjust if you have both 3 and 4 letter month names
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-15 : 02:19:54

This code is more robust and allows for more data variations...eg
1-Jan-1980
01-Jan-1980
01-Sept-1980

UPDATE MyTable  
SET columnName = LEFT(columnName, Charindex('-',columnName)-1) + '/' + 
Case Substring( columnName, Charindex('-',columnName)+1, Charindex('-',columnName, Charindex('-',columnName)+1) - Charindex('-',columnName)-1) 
WHEN 'Jan' THEN '01' 
--Other cases here
WHEN 'Sept' THEN '09' 
END + '/' + RIGHT(columnName,charindex('-',reverse(columnName))-1)

If I were you, I'd run a check on the data to make sure the starting values are valid (as expected)...something like this

select LEFT(columnName, Charindex('-',columnName)-1), 
	Substring( columnName, Charindex('-',columnName)+1, Charindex('-',columnName, Charindex('-',columnName)+1) - Charindex('-',columnName)-1),
	RIGHT(columnName,charindex('-',reverse(columnName))-1)
from MyTable

Hope that helps

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-09-15 : 02:33:22

Thx!
^-^

- HELP -

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources