Convert string with Year and Month ONLY... to the date format for Year and Month only

Question

Bit of a weird ask, but I have rows and rows of year/months as a string and i'm trying to convert it into date format.

Not too sure this can be done as it's not a full date but worth an ask.

UPDATE table SET [Year Month] = CONVERT (DATE, [Year Month], 103)

Returns

Conversion failed when converting date and/or time from character string.

String of data looks like this:

|Year Month| | 2015-02 | | 2016-06 | | 2016-01 | | 2020-09 |

Have seen other posts but they all stem from having the full date already and just essentially cutting of the day to grab the year month etc. but in this case its only year and month that I have as a string and is needed to format into date year and month

what's the date that you want? 01 or end of month? or something else? — gotqn
– gotqn, Commented Nov 3, 2017 at 15:48
i just want to return the same data above but replace it as a date format instead of string — VS1SQL
– VS1SQL, Commented Nov 3, 2017 at 15:50
Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206.0 (X64) Jul 6 2017 07:55:03 Copyright (c) Microsoft Corporation Express Edition (64-bit) — VS1SQL
– VS1SQL, Commented Nov 3, 2017 at 15:50
@VS1SQL In order to have it as DATE it must be a valid date. And the valid date has day defined. — gotqn
– gotqn, Commented Nov 3, 2017 at 15:51

Indent · Accepted Answer · 2017-11-03 16:07:13Z

You need choose a day (here the first of the month) to convert a text field containing a couple year/month to a valid date field :

SQL Server (convert an ISO date YYYY-MM-DD, YYYY/MM/DD, YYYYMMDD... to a date):

UPDATE table SET [Year Month Day] = cast([Year Month] + '-01' as DATE )

Oracle (you can explicitly choose the format):

UPDATE table SET YearMonthDay = TO_DATE(YearMonth || '-01', 'YYYY-MM-DD' )

SQLChao · Accepted Answer · 2017-11-03 15:54:41Z

You can use DATEFROMPARTS starting in SQL Server 2012 but you have to specify the day for it to be a DATE

--set to first day of month UPDATE table SET [Year Month] = DATEFROMPARTS ( LEFT([Year Month], 4), RIGHT([Year Month], 2) , 1 )

EXAMPLE

DECLARE @Year_Month VARCHAR(7) = '2015-02' SELECT DATEFROMPARTS ( LEFT(@Year_Month, 4), RIGHT(@Year_Month, 2) , 1 )

Collectives™ on Stack Overflow

Convert string with Year and Month ONLY... to the date format for Year and Month only

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related