x

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.

more ▼

asked Jan 25, 2010 at 02:16 PM in Default

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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")

more ▼

answered Jan 26, 2010 at 05:39 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

(comments are locked)
10|1200 characters needed characters left

Try this:

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

answered Jan 25, 2010 at 04:00 PM

Mister Magoo gravatar image

Mister Magoo
1.8k 2 3 5

+1 - thanks for the .ToString("dd/MMM/yyyy")
Jan 26, 2010 at 05:39 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x547
x35

asked: Jan 25, 2010 at 02:16 PM

Seen: 1705 times

Last Updated: Jan 25, 2010 at 06:48 PM