Last post Mar 19, 2019 09:00 AM by yrb.yogi
Mar 18, 2019 07:19 PM|Gopi.MCA|LINK
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
Mar 19, 2019 02:49 AM|Nan Yu|LINK
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 :
Mar 19, 2019 04:06 AM|Gopi.MCA|LINK
This is my table data script, i have more then thousands of record
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
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
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
I want to to change Retrun_Date column datatype from Date To datetime
I used your code i got error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Mar 19, 2019 09:00 AM|yrb.yogi|LINK
Use ALTER COMMAND to change the datatype.
ALTER TABLE Sales_Online_Return
ALTER COLUMN Return_Date DATETIME;