Skip to main content

How to Change the Data Type of a Column Using T-SQL


There are some cases that you need to change the data type of a field in SQL Server by not using the Management Studio Tools. The reason is if your database is replicated. You cannot change it directly using the management studio tools so your best option is to use T-SQL. In my case I have a column name “TransDate” that has a datetime datatype. Now when I create a report and filter the record using the date column I cannot filter it easily by using a value like: "{qry_rpt_SalesReportPerItem.TransDate} IN '" & Format(dtpBegDate.Value, "MM/dd/yyyy") & "' TO '" & Format(dtpEndDate.Value, "MM/dd/yyyy") & "'" This could return an error “A date-time is required here.” So the solution is to change the data type from datetime to date. To do this all you have to do is create a “New Query” file and run this code using the ALTER TABLE statement: ALTER TABLE invoice ALTER COLUMN TransDate date In this case you do not need to drop the column in your table.

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.