CODEWITHSUNDEEP
sql-server
theLuckyOne
theLuckyOne

Reputation: 272

Get start and end date by month name, not month number

How do I get start date and end date of a month by month name? Not by date, not by month number.

For example, this gets me first day of current month. Input is a date type, not string like I want it to be.

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

What I want is to input month name in string like 'March' not 3 and get 2016/03/01 and 2016/03/31. I'd really be overwhelmed and appreciate any/all help.

Upvotes: 3

Views: 1159

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can create a CTE to get the month number:

DECLARE @month VARCHAR(10) = 'March';

WITH CteMonths(n, m) AS(
    SELECT 1, 'January' UNION ALL
    SELECT 2, 'February' UNION ALL
    SELECT 3, 'March' UNION ALL
    SELECT 4, 'April' UNION ALL
    SELECT 5, 'May' UNION ALL
    SELECT 6, 'June' UNION ALL
    SELECT 7, 'July' UNION ALL
    SELECT 8, 'August' UNION ALL
    SELECT 9, 'September' UNION ALL
    SELECT 10, 'October' UNION ALL
    SELECT 11, 'November' UNION ALL
    SELECT 12, 'December'
)
SELECT
    DATEADD(MONTH, n - 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)),
    DATEADD(DAY, -1, DATEADD(MONTH, n, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)))
FROM CteMonths
WHERE m = @month

Upvotes: 4

Related Questions