CODEWITHSUNDEEP
sqlsql-serverdatetimedate-formatting
VS1SQL
VS1SQL

Reputation: 155

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

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

Upvotes: 4

Views: 8528

Answers (2)

Indent
Indent

Reputation: 4967

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

Upvotes: 1

SQLChao
SQLChao

Reputation: 7837

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 )

Upvotes: 0

Related Questions