report parameter to get first of current month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

report parameter to get first of current month

Author

Topic

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-28 : 23:26:14

Hi everyone,

in my report parameter in the layout page, how do i get the first day of the previous month (eg august as now is sept), in the 'start' parameter in the default value segment. in the preview tab, the start textbox that i have should display 1/8/2011. and also the last day of the previous month in the 'end' parameter. in the preview tab, the end textbox should display 31/8/2011.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 23:41:28

you can do like
startdate
DATEADD(MONTH,DATEDIFF (MONTH, #1/1/1900#, Now())-1, #1/1/1900#)
end date
DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF (MONTH, #1/1/1900#, Now()), #1/1/1900#))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-28 : 23:54:56

thanks..

i have another problem.

select distinct left(convert(varchar,atb,112),6) as year_mth,
right(convert(varchar,atb,106),8) as year_mth_label
from db
where left(convert(varchar,atb,112),6) >= 201101
order by year_mth desc

when i have this codes, in the preview tab, it display only dates which there are records in it. i want it to display all the month from 201101 to the current month. how do i do that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-29 : 00:03:41

you need to create a calendar table for that and left join your table to it on date

see code below

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-29 : 00:09:02

in the report parameter how do i write the default values in the non-query so that it displays the 201109 (current month) in the preview tab?

-----------

is there any other way to do it? like just code it in the dataset?

cause i do not have permission to add new things

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-29 : 00:22:06

do as earlier suggestion and then use substring to get only year and month part

you can create the udf as given in posted link and then create the query based on it in dataset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-29 : 02:04:22

if i use the codes in your blog, how do i combine it with my codes?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-29 : 08:09:48

what do you mean by combine with your codes? whats your current code?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources