CODEWITHSUNDEEP
sqlsql-servert-sql
Unknown
Unknown

Reputation: 77

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

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

enter image description here

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!

Upvotes: 1

Views: 448

Answers (3)

Karthik Kunala Venu
Karthik Kunala Venu

Reputation: 1

The eomonth() function does the job :)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use eomonth():

select day(eomonth(convert(date, yyyymm + '-01')))

Here is a db<>fiddle for doubters.

Upvotes: 4

Related Questions