CODEWITHSUNDEEP
sqlsql-serverdate
Amol
Amol

Reputation: 1461

From Month Name and year Get date range of that month in sql

I have Month Name And Year from that I want Date Range of that Month.

Input

@Input=August 2014

Output

@fromDate=08/01/2014(MM/dd/YYYY)
@toDate=08/31/2014

Please Help me to do this

Upvotes: 3

Views: 1343

Answers (2)

Minal Birari
Minal Birari

Reputation: 26

Try This

DECLARE @SystemDate DateTime, @StartDate DateTime, @EndDate DateTime
declare @monthid int
SELECT @monthid=MONTH(CAST(@Month+ '1 2010' AS datetime)) 

SET @SystemDate = cast(@monthid as varchar(20))+'-'+'1'+ '-'+cast(@year as varchar(20))

SELECT @StartDate = DATEADD(dd, -Day(@SystemDate) + 1, @SystemDate)
SELECT @EndDate = CONVERT(VARCHAR(20), DATEADD(dd, -(DAY(DATEADD(mm, 1, @SystemDate))),DATEADD(mm, 1, @SystemDate)),101)
SELECT @StartDate StartDate, @EndDate EndDate

Upvotes: 1

Andomar
Andomar

Reputation: 238048

In MySQL, you can use str_to_date to convert a string to a date. Use %M for a named month and %Y for a year:

select  str_to_date('August 2014', '%M %Y') dt
-->
2014-08-00

Since the first day of a month is always the first, it can be found like:

date_format(dt ,'%Y-%m-01')
-->
2014-08-01

The last_day function returns the last day of the month. I'm passing in the first day, because the last_day function doesn't like the 00 day part returned by str_to_date:

last_day(date_format(dt ,'%Y-%m-01'))
-->
2014-08-31

Combine it all:

select  date_format(dt ,'%Y-%m-01')
,       last_day(date_format(dt ,'%Y-%m-01'))
from    (
        select  str_to_date('August 2014', '%M %Y') dt
        ) as SubQueryAlias
-->
2014-08-01      2014-08-31

Upvotes: 3

Related Questions