Selecting first and last day of 2 separate months only given MM-YYYY date

I've been in the process of helping someone change crystal reports and both of us have semi knowledge on using it, at least enough to get by for this one-time help.

Something they've asked us to change was to remove the ability to enter a day in MM/YYYY-MM/YYYY format. We're trying to figure out how to select the first day of the first date section and the very last day/hour of the last date section.

I'v开发者_JAVA百科e seen a few functions like dateadd() with other functions inside which, in the end produce the last or first day of the current month. But i'm unsure how to enter the date we're given (MM/YYYY) into the function to grab the first/last days.

Assuming your parameter's name is {?dates}

{@starting_date} formula:

//extract MM/YYYY from 'MM/YYYY-MM/YYYY'
local stringvar MMYYYY := Split({?dates},"-")[1];
//extract MM from 'MM/YYYY'
local numbervar MM := ToNumber(Split(MMYYYY,"/")[1]);
//extract YYYY from 'MM/YYYY'
local numbervar YYYY := ToNumber(Split(MMYYYY,"/")[2]);
//return first day of selected date
Date(YYYY,MM,1);

{@ending_date} formula:

//extract MM/YYYY from 'MM/YYYY-MM/YYYY'
local stringvar MMYYYY := Split({?dates},"-")[2];
//extract MM from 'MM/YYYY'
local numbervar MM := ToNumber(Split(MMYYYY,"/")[1]);
//extract YYYY from 'MM/YYYY'
local numbervar YYYY := ToNumber(Split(MMYYYY,"/")[2]);
//calculate first day of selected month, add a month, subtract a day, return last day of selected month
DateAdd("m",1,Date(YYYY,MM,1))-1;

declare @date1 varchar(7),@date2 varchar(7)
set @date1='01/2011'
set @date2='05/2011'

-- First convert your dates to YYYYMM
set @date1=RIGHT(@date1,4)+LEFT(@date1,2)
set @date2=RIGHT(@date2,4)+LEFT(@date2,2)

-- Then calculate your limit dates
-- The final date is obtained by adding 1 month to the first day of this month
-- and then substracting 1 second to this date
select CONVERT(datetime, @date1+'01',112) InitialDate,
       CONVERT(datetime, dateadd(ss,-1,dateadd(mm,1,@date2+'01')),112) FinalDate