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：

This is a trick question. There is no date format without values for all three (day, month, and year). This fails:

CREATE TABLE #fake_date (fake_date DATETIME)
INSERT #fake_date
SELECT FORMAT(GETDATE(), 'yyyy-MM')

SELECT * FROM #fake_date
DROP TABLE #fake_date

However, you can display year-month as a varchar by simply using the FORMAT function:

SELECT FORMAT(GETDATE(), 'yyyy-MM')

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')