My table contains daily quantities for different products. In my query, I'm trying to convert date (12/28/18) to the Excel value equivalent (43,462) and then combine it with the product name (ABC).
I tried different versions of concatenate and CAST but seem to be missing something.
Expected output is 43642-ABC
You could use:
CREATE TABLE t(d DATETIME, c VARCHAR(10));
INSERT INTO t(d,c) VALUES('12/28/18', 'ABC');
SELECT *, CONCAT(DATEDIFF(day, '19000101', d)+2,'-',c) AS result
FROM t
Output:
+----------------------+------+-----------+
| d | c | result |
+----------------------+------+-----------+
| 28/12/2018 00:00:00 | ABC | 43462-ABC |
+----------------------+------+-----------+
Explanation why two was added to DATEDIFF's result: Difference between datetime converts in MSExcel and SQL Server
