sql server 2008 convert field datatype from datetime2 to smalldatetime

Posted on 2012-09-07
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

    Author Comment

    still need an answer
    LVL 92

    Expert Comment

    by: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


    Author Comment

    can I do ALTER TABLE SomeTable ALTER COLUMN SomeDate smalldatetime
    to change all 1000 records data type
    LVL 92

    Accepted Solution

    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

    thanks for speedy responses and excellent info each time!

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now