Formatting a parameter

Question

question

Fatherjack asked Jan 25, '10

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-2005 ssrs bids

10 |1200

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

Answer 1 · 2010-01-26T05:39:15Z

Fatherjack answered Jan 26, '10

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

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

Answer 2 · 2010-01-25T16:00:33Z

Mister Magoo answered Jan 25, '10

Try this:

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

1

10 |1200

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

Fatherjack ♦♦ commented · Jan 26, 2010 at 05:39 AM

+1 - thanks for the .ToString("dd/MMM/yyyy")

0 ·

question