Link to home
Start Free TrialLog in
Avatar of LBecerra
LBecerraFlag for United States of America

asked on

sql server 2008 convert field datatype from datetime2 to smalldatetime

Hello!

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.

Thanks
Avatar of LBecerra
LBecerra
Flag of United States of America image

ASKER

still need an answer
Avatar of Patrick Matthews
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

can I do ALTER TABLE SomeTable ALTER COLUMN SomeDate smalldatetime
to change all 1000 records data type
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks for speedy responses and excellent info each time!