Convert datetime columns to smalldatetime in whole database

Hi,

I need to convert all datetime columns to smalldatetime in the whole database. I really don't want to do it by hand and It would probably take me a whole day to figure out how to write such a procedure. If someone could help me out that would be great.
My database is divided into schemas just like AdventureWorks.
Also, no need to worry about date conversion, value could be set to current date.

Thanksmay i ask a stupid question?

why?|||That is a stupid question.

Choose my answer:

Why not?
Because
Cause I feel like it
All of the above|||1. because changing it is pointless
2. ok
3. good for you
4. all of the above

have fun stormin' the castle...|||That is a stupid question.

Choose my answer:

Why not?
Because
Cause I feel like it
All of the above

Fill in the blank: Kiss my ____________.|||Fill in the blank: Kiss my ____________.

Asphalt .|||That is a stupid question.

Oh, foolesh young jedi|||I really don't want to do it by hand

Then you should look into better grooming and more social skills

I'm sure you can find a date if you try hard enough

Lobster and champange work pretty well if you can master the other stuff|||Everyone seems really mean these days. Or am I just noticing it having come back?|||Everyone seems really mean these days. Or am I just noticing it having come back?

That is a stupid question

(So how does that feel?)|||That is a stupid question

(So how does that feel?)Waaaaaaaahhhhhhhhhhhhhhhh!!!!!!!!! :eek:|||That is a stupid question.

Choose my answer:

Why not?
Because
Cause I feel like it
All of the above

The list is not complete

You left off ignorance|||i am not mean

i would like to go back and try to re-start this friendly dialogue

dear polarbear2k, why do you want to do it?|||i am not meanI didn't mean you. it was a perfectly valid question to ask - the parameters of the solution were included in the original post but not the parameters of the problem.|||If you must know its a legacy software issue. A program we have craps out when you pass it seconds. Formatting the output is an option but preventing the problem in the first place is a better solution.

We really need the extra 4 bytes freed up. :p

Everybody is so mean. lol|||seems to me it would be easier to fix the program that's crapping out :D

you said "no need to worry about date conversion, value could be set to current date" -- how does wiping out all existing datetime values solve the problem?|||That's fair enough. So you need to round the datetimes off to the nearest minute, then convert the data type. The latter is not *strictly* required but cleaner and more thorough right?|||Also, you'll need use of system tables. 2000 or 2K5? If 2K5 then SP2 (this has a handy schema function you see)?|||And you were mean - Rudy is a very sensitive soul.|||Nobody who wears leather pants can be that sensitive.|||Hi,
I need to convert all datetime columns to smalldatetime in the whole database.
I'd suggest scripting out the entire database, including tables, sprocs, functions, constraints, everything, into a single DDL file. Search and replace on the DDL file to change your datatype. Execute the script to create a new database. Use DTS/SSIS/Export Data Wizard to transfer data from old database to new database.|||WARNING, WARNING, WARNING!!!

Using this script without having a current resume, multiple good database backups, and a good understanding of what this script is going to do is DANGEROUS!-- ptp 20071108 See http://www.dbforums.com/showthread.php?t=1624169

DECLARE
@.cCmd NVARCHAR(2000)
, @.cColumn sysname
, @.cNull NVARCHAR(10)
, @.cTable sysname

DECLARE zFullDates CURSOR FOR SELECT
TABLE_NAME, COLUMN_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 'datetime' = DATA_TYPE

OPEN zFullDates

FETCH zFullDates INTO @.cTable, @.cColumn, @.cNull

WHILE 0 = @.@.fetch_status
BEGIN
SET @.cCmd = 'ALTER TABLE ' + QuoteName(@.cTable)
+ ' ALTER COLUMN ' + QuoteName(@.cColumn)
+ ' SMALLDATETIME '
+ CASE WHEN N'No' = @.cNull THEN 'NOT NULL' ELSE 'NULL' END

EXECUTE (@.cCmd)
FETCH zFullDates INTO @.cTable, @.cColumn, @.cNull
END

CLOSE zFullDates

DEALLOCATE zFullDates-PatP|||No need to worry about the values because we're setting up a new db on SQL Server 2005.
Changing the program is not possible.

Thanks for the script. I added the support for schemas, but unfortunately the server won't let me change the column when another object depends on it, like a constraint.

I guess the easiest way would be to do what blindman suggested. Script the whole db to a DDL file, find & replace, recreate the db.

Thanks|||RI Is goining to be a beach

Script the Table DDL From the constraints, do the ddl, the load, the apply the constraints|||Wow, that turned out pretty well, even without the group hug I would have thought necessary.|||he really should fix the code...if it's a third party vendor, then ask for your money back|||the third party vendor went out of business in 1977|||oh, sorry. The "Value add strategic partner", then|||Sounds like a new BE for a legacy FE. "Rewrite the FE" isn't really all that useful advice for the fella is it?
Blindman's solution is soup herb, particulalry with there being no data.|||Sounds like a new BE for a legacy FE. "Rewrite the FE" isn't really all that useful advice for the fella is it?
Blindman's solution is soup herb, particulalry with there being no data.

smoking crack again?|||smoking crack again?Heroin - it has a mellower taste.|||it haz a flavr (http://icanhascheezburger.com/?s=flavr)

:cool: