update all datetime fields in a database

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)

update all datetime fields in a database

Author

Topic

ShooterJ07
Starting Member

17 Posts

Posted - 2008-11-03 : 16:52:25

I am trying to take all datetime fields in my database, and increment the year by 1.

I've ran this query to find all tables and each field that is a datetime field:

SELECT table_name, column_name
FROM information_schema.columns
WHERE data_type='datetime'

The results might look like this:

TableName, FieldName
-------------------
Threads,PostDate
Replies,ReplyDate

From there, I've pasted the results in to Excel and concatenated a statement for each row to increment the date. From Excel, I've produced this:

UPDATE Threads SET PostDate = DATEADD(Year,1,PostDate)
UPDATE Replies SET ReplyDate = DATEADD(Year,1,ReplyDate)

My question, is how can I use T-SQL to accomplish this, without using goofy Excel to produce a bunch of individual UPDATE statements? Basically I'm looking for some sort of for/each loop of a thing...

Thanks for any help..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-03 : 16:55:24

[code]SELECT 'UPDATE ' + QUOTENAME(table_name) + ' SET ' + QUOTENAME(column_name) + ' = DATEADD(YEAR, 1, ' + QUOTENAME(column_name) + ')'
FROM information_schema.columns
WHERE data_type IN ('datetime', 'smalldatetime')[/code]

E 12°55'05.63"
N 56°04'39.26"

ShooterJ07
Starting Member

17 Posts

Posted - 2008-11-03 : 16:58:29

Thanks Peso. Sorry if I wasn't clear in my original post..

I'm not really lookin for a way to produce a bunch of update statements. I'm looking for a way to perform the update without producing a bunch of UPDATE statments. It's not a huge deal - the way I did it the first time and the recommendation you gave gets the job done... I'm just trying to learn a better way.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-03 : 17:01:32

And if you don't want to copy the result to a query window and run the result...

DECLARE	curYak CURSOR FOR
		SELECT	'UPDATE ' + QUOTENAME(table_name) + ' SET ' + QUOTENAME(column_name) + ' = DATEADD(YEAR, 1, ' + QUOTENAME(column_name) + ')'
		FROM	information_schema.columns
		WHERE	data_type IN ('datetime', 'smalldatetime')

DECLARE	@SQL VARCHAR(8000)

OPEN curYak

FETCH	NEXT
FROM	curYak
INTO	@SQL

WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT	@SQL
		EXEC	(@SQL)

		FETCH	NEXT
		FROM	curYak
		INTO	@SQL
	END

CLOSE curYak
DEALLOCATE curYak

E 12°55'05.63"
N 56°04'39.26"

ShooterJ07
Starting Member

17 Posts

Posted - 2008-11-03 : 17:02:48

Thanks again, Peso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources