Showing posts with label enddate. Show all posts
Showing posts with label enddate. Show all posts

Saturday, February 25, 2012

Defaulting Start and End dates to the previous month.

I have two parameters in my report (StartDate and EndDate). I want to default these parameters to the previous month.

For example... If today is 5/17/2007, I want StartDate to be 4/1/2007 and EndDate to be 4/30/2007. If today would be January 30th 2007, I would want StartDate to be 12/1/2006 and EndDate to be 12/31/2006.

How can I do this?

Nevermind... I used the following two functions. However, if you know of a better way to do this, please let me know.

Public Function EndDt() As Date

Dim Today As Date = Date.Today

Dim Day As Integer = Today.Day

Dim EndDate As Date

EndDate = Today.AddMonths(1)

EndDate = Today.AddDays(-Day)

Return EndDate

End Function

Public Function StartDt() As Date

Dim Today As Date = Date.Today

Dim Day As Integer = Today.Day - 1

Dim StartDate As Date

StartDate = Today.AddMonths(-1)

StartDate = Today.AddDays(-Day)

Return StartDate

End Function

|||

I can't think of a quick direct way

but I do want to suggest using BETWEEN, and first of month (so that you don't have to worry about # of days in a month)

e.g. for 05/17/2007

you'd want SQL code to be

Code Snippet

column BETWEEN 04/01/2007 AND 05/01/2007

since BETWEEN is inclusive, but only up to '05/01/2007 00:00:00', anything after 05/01/2007 midnight will NOT show up

so in backend SQL

it'll be like

Code Snippet

SELECT
previous_month = CAST( MONTH(getdate())-1 AS varchar) + '/01/' + CAST(YEAR(getdate()) AS varchar)
, current_month = CAST( MONTH(getdate()) AS varchar) + '/01/' + CAST(YEAR(getdate()) AS varchar)

then you get

previous_month current_month

4/01/2007 5/01/2007

Friday, February 24, 2012

Default values for StartDate and EndDate

Hi,
I have Start date and End date as parameters. Now is there a way to have the
start date always default to the start date of the current month...i.e
1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
month & current year?
How can this be done?
Thanks
--
pmudHi,
I have solved half the question reading from other posts. For the start date
I used =DateTime.Now.Addmonths(-1) . So it went back to the previous month.
But how do i set the day to 30 ?
--
pmud
"pmud" wrote:
> Hi,
> I have Start date and End date as parameters. Now is there a way to have the
> start date always default to the start date of the current month...i.e
> 1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
> month & current year?
> How can this be done?
> Thanks
> --
> pmud|||Enter this for your (Non-Queried) parameter defaults:
StartDate... (this is in the format "m/d/yyyy" which could be changed)
=CDate(Month(Now()).ToString & "/1/" &
Year(Now()).ToString).ToShortDateString
EndDate...(This takes the first day of the next month and subtracts one day.)
=DateAdd(DateInterval.Day, -1, (DateAdd(DateInterval.Month, 1,
CDate(Month(Now()).ToString & "/1/" & Year(Now()).ToString)
))).ToShortDateString
Hoep this helps. If anyone has a better solution I'd be happy to see it.
Fred
"pmud" wrote:
> Hi,
> I have Start date and End date as parameters. Now is there a way to have the
> start date always default to the start date of the current month...i.e
> 1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
> month & current year?
> How can this be done?
> Thanks
> --
> pmud|||Hi,
I put the logic into a Stored procedure and created a new dataset in the
report designer that references this SP. Then just reference the dataset in
the report paramaters section.
It suited what I needed here as multiple reports have the same default start
and end dates so rather than creating logic in each report the single SP will
do for all the reports. But that may not suit others.
"FredP" wrote:
> Enter this for your (Non-Queried) parameter defaults:
> StartDate... (this is in the format "m/d/yyyy" which could be changed)
> =CDate(Month(Now()).ToString & "/1/" &
> Year(Now()).ToString).ToShortDateString
>
> EndDate...(This takes the first day of the next month and subtracts one day.)
> =DateAdd(DateInterval.Day, -1, (DateAdd(DateInterval.Month, 1,
> CDate(Month(Now()).ToString & "/1/" & Year(Now()).ToString)
> ))).ToShortDateString
> Hoep this helps. If anyone has a better solution I'd be happy to see it.
> Fred
>
> "pmud" wrote:
> > Hi,
> >
> > I have Start date and End date as parameters. Now is there a way to have the
> > start date always default to the start date of the current month...i.e
> > 1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
> > month & current year?
> >
> > How can this be done?
> >
> > Thanks
> > --
> > pmud