sql server 2008 convert field datatype from datetime2 to smalldatetime


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.

Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:
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).
LBecerraAuthor Commented:
still need an answer
Patrick MatthewsCommented:
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

LBecerraAuthor Commented:
can I do ALTER TABLE SomeTable ALTER COLUMN SomeDate smalldatetime
to change all 1000 records data type
LBecerraAuthor Commented:
thanks for speedy responses and excellent info each time!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.