Get the solution ↓↓↓
I need to change columndatatype without affecting data in that column. I have table structure below:
CREATE TABLE [dbo].[PROJECT_LOG](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UNIT] [int] NULL,
[NAME] [nvarchar](100) NULL,
[STATUS] [numeric](1, 0) NULL,
[LOG] [nvarchar](200) NULL,
[LAST_UPDATE] [nvarchar](100) NULL
) ON [PRIMARY]
GO
Now, I have around 200 records below:
I want to change columnLAST_UPDATE (current mm/dd/yyyy format) todatetime2. Can anyone help me with this?
I tried using convert query for the same as the guys suggested below and answer here.
-- Add new column.
ALTER TABLE [dbo].[PROJECT_LOG] ADD LAST_UPDATE_TIME DATETIME;
-- Convert value.
UPDATE [dbo].[PROJECT_LOG]
SET LAST_UPDATE_TIME = CONVERT(nvarchar,
REPLACE(LEFT(LAST_UPDATE, 11), '/', ' ') + ' ' + RIGHT(LAST_UPDATE, 8), 101);
While executing query its throwing error:
Changing the table
The approach:
Important note: Check all the import scripts to this table to fix the functions used to set LAST_UPDATE.
Alternative
Important note: If you get any other date format other than US then this formula breaks as it explicitly expects the 101 US format.
View as crazy alternative
Build a view on top of this table that does the transformation. In SQL Server 2008 there is no TRY_CAST function to fail graciously.
Use the view for downstream work.
Why date?
Type date costs 3 bytes and is perfect for date only values.
datetime2(0) costs 6 bytes, the default datetime2(7) costs 8 bytes.
References:
Cast and Convert https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15
Datetime2 https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver15
Try_Cast https://docs.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-ver15
Since this you are using the 2008 version try_cast will be not helpful.
One safe method will be to implement the update with a loop and go through the entire table row by row with a try catch block within the loop where you can handle in case of any failure at the time of casting , in catch block you can give the query to update the value as NULL to identify if casting failed.
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Find the answer in similar questions on our website.
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.
PHP (from the English Hypertext Preprocessor - hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites.
The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
https://www.php.net/
Welcome to the Q&A site for web developers. Here you can ask a question about the problem you are facing and get answers from other experts. We have created a user-friendly interface so that you can quickly and free of charge ask a question about a web programming problem. We also invite other experts to join our community and help other members who ask questions. In addition, you can use our search for questions with a solution.
Ask about the real problem you are facing. Describe in detail what you are doing and what you want to achieve.
Our goal is to create a strong community in which everyone will support each other. If you find a question and know the answer to it, help others with your knowledge.