[Webinar] Learn how to a build a cloud-first strategyRegister Now


sql server 2008 convert field datatype from datetime2 to smalldatetime

Posted on 2012-09-07
Medium Priority
Last Modified: 2012-09-07

I have a table of 1000 records in sql server 2008 that I migrated from an MS access database used as a backend.. The dates are showing up as text in MS access front end connected to the sql back end table.

Solution is to convert the date fields in the SQL server table that have datatype =datetime2 to the datatype called smalldatetime. This is proven to work with a few records testing.

Can you please write the code to convert all 1000 records to new datatype.

Our db administrator does not know how and has tryed CAST , ALTER to no avail so we need the exact code.

Question by:LBecerra
  • 3
  • 2

Author Comment

ID: 38377707
still need an answer
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38377846
To actually change the table to use the new data type:

ALTER TABLE SomeTable ALTER COLUMN SomeDate smalldatetime

Open in new window

To change it just in a query result set:

SELECT CONVERT(smalldatetime, SomeDate) AS SomeDate
FROM SomeTable

Open in new window


Author Comment

ID: 38377864
can I do ALTER TABLE SomeTable ALTER COLUMN SomeDate smalldatetime
to change all 1000 records data type
LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 38377897
Yes, as long as the values in the column you are changing fit within the acceptable range for smalldatetime (1900-01-01 through 2079-06-06).

Author Closing Comment

ID: 38378037
thanks for speedy responses and excellent info each time!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question