CODEWITHSUNDEEP
sqlsql-serverstringt-sqldate
vivianna
vivianna

Reputation: 99

Combine 2 String Columns Like Year and Month to a Date Format Column

I updated the expected date format. Since there is no day column, it will add the available last day for each month. for example, July has 31 days, then it add 31 days to July, like 2012-07-31; June has 30 days, it will become to 2012-06-30.

I have a dataset in SQL Server. The year and month are 2 separate columns. They are both strings.

Year  Month
2012    7
2010    6
2009    1
2009    3
2018    10

How can I combine them together into a new date format column like:

Year  Month   Date
2012    7    2012-07-31 00:00:000
2010    6    2010-06-30 00:00:000
2009    1    2009-01-31 00:00:000
2009    3    2009-03-31 00:00:000
2018    10   2018-10-31 00:00:000

I tried a few ways but keep getting the error:

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

Upvotes: 0

Views: 4743

Answers (2)

GMB
GMB

Reputation: 222432

If you are looking to generate a new string that concatenates the numeric years and months, you use string functions:

concat(year, '-', case when len(month) = 1 then '0' end, month)

The case expression left pads the month with 0 when it has just one digit.

Now the question was edited and it appears that you want the last day of the month. If so, you can use datefromparts() to generate a date that represents the first day of the month, and eomonth() to get the last day of that month:

eomonth(datefromparts(year, month, 1))

And if you really want a datetime instead of a date:

cast(eomonth(datefromparts(year, month, 1)) as datetime)

Demo on DB Fiddle:

year | month | endofmonth             
---: | ----: | :----------------------
2012 |     7 | 2012-07-31 00:00:00.000
2010 |     6 | 2010-06-30 00:00:00.000
2009 |     1 | 2009-01-31 00:00:00.000
2009 |     3 | 2009-03-31 00:00:00.000
2018 |    10 | 2018-10-31 00:00:00.000

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can construct a date using datefromparts():

select datefromparts(year, month, 1)

You would then have to format the date into a string to get it in yyyy-mm format.

If you want a column with that format, use one of the following:

select concat(year, '-', right('0' + month, 2))

or: select year * 100 + month

depending on whether the base columns are strings or numbers.

Upvotes: 1

Related Questions