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

Submitted by: 
Visitors have accessed this post 5514 times.
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:
  1. "{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:
  1. ALTER TABLE invoice ALTER COLUMN TransDate DATE
In this case you do not need to drop the column in your table.

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.