locked
Change Table Date Fileds To Datetime without data lose RRS feed

  • Question

  • User-807418713 posted

    Hello

    I have one field in Table for example Invoice_Date before this coumn has date : datatype

    now i want this datatype to be : datetime how to change using sql code..? without data lost

    Thanks

    Monday, March 18, 2019 7:19 PM

Answers

  • User-2082239438 posted

    Use ALTER COMMAND to change the datatype.

    ALTER TABLE Sales_Online_Return
    ALTER COLUMN Return_Date DATETIME;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 19, 2019 9:00 AM

All replies

  • User1724605321 posted

    Hi Gopi.MCA ,

    Date Only keeps track of days, starting 01/01/01 to 31/12/9999. Takes 3 bytes for storage.

    While DateTime is used to store date and time between 01/01/1753 to 31/12/9999.
    Minimum unit of time it keeps is milliseconds with an accuracy of 3.33 ms. Takes 8 bytes for storage.  So i don' t think convert date to datetime will make data lost , just use SSMS to change your data type .

    If you want to convert it in t-sql , just try :

    CONVERT(DATETIME,[DateOnlyColName]) 

    Best Regards,

    Nan Yu

    Tuesday, March 19, 2019 2:49 AM
  • User-807418713 posted

    Hello

    This is my table data script, i have more then thousands of record

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Sales_Online_Return](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Return_Date] [date] NULL,
    	[SKU_BC] [varchar](250) NULL,
    	[Return_Online_Qty] [float] NULL,
     CONSTRAINT [PK_Sales_Online_Return] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    

    I want to to change Retrun_Date column datatype from Date To datetime

    I used your code i got error

    CONVERT(DATETIME,Return_Date)

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'CONVERT'.

    Tuesday, March 19, 2019 4:06 AM
  • User-2082239438 posted

    Use ALTER COMMAND to change the datatype.

    ALTER TABLE Sales_Online_Return
    ALTER COLUMN Return_Date DATETIME;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 19, 2019 9:00 AM