How can I get Days and Months from YYYY-mm Format?

Question

I have many varchar YYYY-mm values in my table :

But now I need days in Month from this.

I need the result: 2018-04 -> 30 Days

So how I can achieve this?

I thank you in advance!

Answer 1

In addition to Gordon's spot-on answer, here is another way to compute the number of days in the month:

SELECT
    DATEDIFF(day,
             TRY_CONVERT(datetime, date+'01'),
             DATEADD(month, 1, TRY_CONVERT(datetime, date+'01')))
FROM yourTable;

Demo

Actually, if you have a long term need for the number of days in each calendar month over some range, you might want to just create a calendar table with this fixed table. Then, you may join to it whenever you need it.

Answer 2

The eomonth() function does the job :)