Formatting Date Parameters

Question

Formatting Date Parameters

eseosaoregie

SSCertifiable

Points: 6889
More actions
November 5, 2009 at 5:26 am

#227706

I currently have a report that uses a dataset to create a list of dates for paramater drop down list. Currently the format of the date is dd/mm/yyyy. However I would like the format to be mon-yy e.g. aug-2009. How can I do this. Do I need to alter the format within the dataset query?
Regards
Sauce1979

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 1

I know squat about reporting services but, hopefully, my response will act as a "bump" for your post, someone who understands SSRS will see it, and you'll get your answer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alasdair Thomson Hall of Fame Points: 3630 More actions · Answer 2

You need to use the FORMAT command in the expression builder so example is "=FORMAT(YourfieldName, "MMM yyyy").

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 3

I am aware of that but how can I specifically apply it for a parameter. That is to say where do I find the expression builder to format the parameter.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

Yes, you are going to have to format it in your dataset. Additionally, you are going to have to include the numeric month to order the dataset - or it will not be in the proper order.

And finally, once you do that - you will no longer have a datetime parameter and will not be able to use the datetime picker. If your procedure or code is using a datetime parameter - then you are going to need to include an actual date for the value - with your external value as the drop-down value.

Oh, one more thing - by doing this, you are going to cause yourself problems if you pass a date formatted as dd/mm/yyyy to SQL Server. Depending upon the server settings - SQL Server will interpret the above date differently. I would recommend that you use values with the format of YYYYMMDD - which is un-ambiguous and will not be misinterpreted by SQL Server.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 5

If I include the numeric month how will I be able to display aug-2009 for example?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

When you setup the dataset - you have two values returned from the query. The first will be the label (what the user sees) and the second will be the actual value passed to your query.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

becklery Hall of Fame Points: 3315 More actions · Answer 7

Hi, eseosaoregie.

In my opinion, maybe you can use RAQ Report to solve your problem.

As I know, it allows users to set format for data easily.

Regards,

becklery.

RAQ Report: Web-based Excel-like Java reporting tool[/url]

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 8

Many thanks for the information. It really helped. Apologies for asking obvious questions but I have been dealing with this report for a long time now and have taken care of the most complex parts of it. This date issue i left to the end as I thought it would be easy to tackle. In any case I have opted for adding a calculated field in my dataset and this seems to work.

Regards

Sauce1979

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 9

becklery (11/8/2009)
Hi, eseosaoregie.
In my opinion, maybe you can use RAQ Report to solve your problem.
As I know, it allows users to set format for data easily.
Regards,
becklery.

Heh... first Dassin, now you. What are you in? An MLM for RAQ?

If you're going to spam us, at least make it complete spam with an actual solution. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)