Excel formula 鍵combines date columns and time columns into 1 columns format as yyyy-mm-dd hh-mm-ss
时间: 2024-05-28 22:10:44 浏览: 77
Assuming your date is in column A and time is in column B, you can use the following formula to combine them into one column:
=TEXT(A1,"yyyy-mm-dd")&" "&TEXT(B1,"hh-mm-ss")
This formula will format the date in column A as "yyyy-mm-dd" using the TEXT function, and the time in column B as "hh-mm-ss" also using the TEXT function. The "&" operator joins the two formatted values together with a space between them.
You can then copy this formula down for the rest of the rows in your worksheet.
相关问题
UPDATE a SET a.refund_status = b.dealstat , a.refund_time = b.dealtime FROM dwi_store_setmt_ord_dtl_p a INNER JOIN sdi_dgmps_dgmpsdb_mstore.chainstorefeemangdtl_p b ON a.comp_code = trim(upper(b.companycode)) AND a.src_pid = trim(b.pid) WHERE a.refund_status = 0 AND ISNULL(a.refund_time,'') = '' AND b.dealstat = 1 AND ISNULL(a.dealtime,'') <> '' ;改为用merge into 更新
Sure, here's the equivalent MERGE statement:
```
MERGE INTO dwi_store_setmt_ord_dtl_p AS a
USING (
SELECT trim(upper(b.companycode)) AS comp_code,
trim(b.pid) AS src_pid,
b.dealstat,
b.dealtime
FROM sdi_dgmps_dgmpsdb_mstore.chainstorefeemangdtl_p AS b
WHERE b.dealstat = 1
) AS b
ON a.comp_code = b.comp_code
AND a.src_pid = b.src_pid
AND a.refund_status = 0
AND ISNULL(a.refund_time, '') = ''
AND ISNULL(a.dealtime, '') <> ''
WHEN MATCHED THEN
UPDATE SET a.refund_status = b.dealstat,
a.refund_time = b.dealtime;
```
This MERGE statement combines the source table `sdi_dgmps_dgmpsdb_mstore.chainstorefeemangdtl_p` with the target table `dwi_store_setmt_ord_dtl_p` based on the matching conditions specified in the ON clause. When a match is found, the specified columns in the target table are updated with the corresponding values from the source table.
Column count mismatch in UNION ALL
Column count mismatch in a SQL statement like `UNION ALL` occurs when you are combining data from two or more tables using the `UNION ALL` clause, and the number of columns (or their datatypes) do not match across the tables being combined.
`UNION ALL` combines rows from multiple SELECT statements into a single result set, treating NULL values as equivalent to an empty cell. Each SELECT statement must have the same number of columns, with compatible data types. If one table has additional columns, or if column counts differ but the columns are not compatible, you'll encounter this error.
For example:
```sql
SELECT A, B, C
FROM Table1
UNION ALL
SELECT D, E
FROM Table2; -- Error: Missing columns C and incompatible type for E
```
To resolve this issue, make sure all SELECT statements in the `UNION ALL` have identical column structures:
- Remove extra columns from the ones with fewer.
- Modify the data types so they align if possible.
- Alternatively, use a `UNION` instead of `UNION ALL`, which automatically removes duplicate rows, allowing mismatches temporarily.