SQL新手求助：如何将DocumentDate字段拆分为DATE和TIME两个新字段

Hey there! Since you're new to SQL, let's break this down step by step—splitting a DocumentDate datetime field into separate date and time columns is a super common task, and the exact code depends on which SQL database you're working with. Below are the most common scenarios to get you sorted:

First, you'll need to add the two new columns to your table:

ALTER TABLE YourTableName 
ADD DATE DATE, TIME TIME;

Then populate them by converting the original DocumentDate field:

UPDATE YourTableName 
SET DATE = CAST(DocumentDate AS DATE),
    TIME = CAST(DocumentDate AS TIME);

If you want these columns to automatically stay in sync with DocumentDate (no manual updates later), you can use persisted computed columns instead:

ALTER TABLE YourTableName 
ADD DATE AS CAST(DocumentDate AS DATE) PERSISTED,
    TIME AS CAST(DocumentDate AS TIME) PERSISTED;

Adding the columns is similar, but we use built-in date/time functions to extract the values:

ALTER TABLE YourTableName 
ADD DATE DATE, TIME TIME;

Then update the data:

UPDATE YourTableName 
SET DATE = DATE(DocumentDate),
    TIME = TIME(DocumentDate);

Note: DATE and TIME are reserved keywords in PostgreSQL, so we'll wrap them in double quotes to avoid errors:

ALTER TABLE YourTableName 
ADD "DATE" DATE, "TIME" TIME;

Then populate the columns using PostgreSQL's type casting syntax:

UPDATE YourTableName 
SET "DATE" = DocumentDate::DATE,
    "TIME" = DocumentDate::TIME;

Quick Tip #

Always make sure to replace YourTableName with the actual name of your table. If you're working with a different database (like Oracle or SQLite), feel free to mention it and I can share the specific code for that system.

内容的提问来源于stack exchange，提问作者Stephen oxley