question

Fatherjack avatar image
Fatherjack asked

Formatting a parameter

What formula should I use to have a SSRS parameter show the first day of the current month as its default value in the format dd/MMM/yyy eg 01/Jan/2010?

I am currently using =cdate(format("short date",DateAdd("d",(Day(now)*-1)+1,Now))) which returns it as 01/01/2010 17:11:16 I have also tried =cdate(format("dd/MMM/yyyy",DateAdd("d",(Day(now)*-1)+1,Now))) with no better effect. I am also going to need a parameter for the last day of the month but its the formatting that is the problem at the moment.

I could easily get this with a call to the SQL Server but it seems a waste.

sql-server-2005ssrsbids
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

Actually ended using =cdate(DateAdd("d",(Day(now)*-1)+1,Now).ToString("dd/MMM/yyyy")) for the first day of this month as the parameter has to be of date type. Many thanks to MisterMagoo for the nudge in the right direction using .ToString("dd/MMM/yyyy")

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mister Magoo avatar image
Mister Magoo answered

Try this:

=dateadd(
	"d",
	1,
	dateadd(
			"m",
			datediff("m","31 dec 1899", Globals!ExecutionTime)-1,
			DateValue("31 dec 1899")
			)
	).ToString("dd/MMM/yyyy")
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
+1 - thanks for the .ToString("dd/MMM/yyyy")
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.