LBecerra
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
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
To actually change the table to use the new data type:
To change it just in a query result set:
ALTER TABLE SomeTable ALTER COLUMN SomeDate smalldatetime
To change it just in a query result set:
SELECT CONVERT(smalldatetime, SomeDate) AS SomeDate
FROM SomeTable
ASKER
can I do ALTER TABLE SomeTable ALTER COLUMN SomeDate smalldatetime
to change all 1000 records data type
to change all 1000 records data type
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for speedy responses and excellent info each time!
ASKER