SQL Server: how to convert YYYY-MM-DD to YYYY-MM?-CodePudding

I feel like this question comes up all the time in SQL Server interviews but I do not know how to answer it. Cannot find a good resource online.

In SQL Server, how do I convert 2019-01-01 or 2019-01-01 HH:MM:SS to just 2019-01?

CodePudding user response：

In MS SQL, how do I convert 2019-01-01 or 2019-01-01 HH:MM: SS to just 2019-01 while keeping it as a date data type? [emphasis added]

You can't. SQL Server's date data type represents a specific calendar day, not a month. 2019-01 is not a day.

(You can, of course, output only the year and month component when converting the date to one of SQL Server's string data types.)

CodePudding user response：

As many already mentioned, SQL Server doesn't have a built-in data type for a 'YYYY-MM'.

Though SQL Server's XML/XQuery/XSD does have it: xs:gYearMonth

For the reference: Constructor Functions (XQuery)

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, dateColumn DATE);
INSERT INTO @tbl (dateColumn) VALUES
('2019-01-01'),
('2019-12-31'),
(NULL);
-- DDL and sample data population, end

SELECT ID, dateColumn
    , result = c.query('/r/text()').value('xs:gYearMonth(substring(.,1,7))', 'CHAR(7)')
FROM @tbl
    CROSS APPLY (SELECT CAST('<r>'   CAST(dateColumn AS CHAR(10))   '</r>' AS XML)) AS t(c);

Output

 ---- ------------ --------- 
| ID | dateColumn | result  |
 ---- ------------ --------- 
|  1 | 2019-01-01 | 2019-01 |
|  2 | 2019-12-31 | 2019-12 |
|  3 | NULL       | NULL    |
 ---- ------------ ---------

CodePudding user response：

Try SELECT CONCAT(year(datefield), '-', month(datefield)) FROM YOURTABLE

CodePudding user response：

to_char(created_at::date, 'YYYY-MM')