Format ParamDisplayValue date value

[ztruelove]

I've got a report expression with the formula

Code Select Expand

'Report Sub Header Here - ' + ParamDisplayValue('Date')

This results in the date being spelled out (e.g., Jan 1, 2018).  What is the proper syntax to format the date in a MM-DD-YYYY format?

[dougp]

Thanks for the practical exercise in macros!

Try this:

The prompt must be for a datetime.

Create a text item with this value:

Code Select Expand

Report Sub Header Here -

To the right of the text item, create a singleton.
To the singleton, add a query calculation with this definition:

Code Select Expand

#    sq(
timestampMask(prompt ('Date', 'token') + '-05:00', 'mm')  + '-' +
timestampMask(prompt ('Date', 'token') + '-05:00', 'dd')  + '-' +
timestampMask(prompt ('Date', 'token') + '-05:00', 'yyyy')
)   #

Adjust your time zone offset as required.

[MFGF]

Thanks for the practical exercise in macros!

Try this:

The prompt must be for a datetime.

Create a text item with this value:

Code Select Expand

Report Sub Header Here -

To the right of the text item, create a singleton.
To the singleton, add a query calculation with this definition:

Code Select Expand

#    sq(
timestampMask(prompt ('Date', 'token') + '-05:00', 'mm')  + '-' +
timestampMask(prompt ('Date', 'token') + '-05:00', 'dd')  + '-' +
timestampMask(prompt ('Date', 'token') + '-05:00', 'yyyy')
)   #

Adjust your time zone offset as required.

Hi,

A simpler approach would be to create the calculation expression in the singleton as ?Date?, then use the data format property to format it in whatever way you like.

Cheers!

MF.

[Invisi]

Where does this Date come from? I tried the formatting with a date from a date prompt and it just gives me the finger and ignores my formatting... No matter if I use ParamValue or ParamDisplayValue.

[MFGF]

Where does this Date come from? I tried the formatting with a date from a date prompt and it just gives me the finger and ignores my formatting... No matter if I use ParamValue or ParamDisplayValue.

Add a query item or calculation with an expression of ?yourDateParameterName?

Put this item into a singleton

Then you can format the item - this isn't possible with a layout calculation using ParamValue or ParamDisplayValue :)

MF.

[ztruelove]

Thanks, all.  Dropping a singleton seems to be the route to go; however, I'm failing on the proper formatting options.  :p

If I go to the Data Format property and choose Date as the Format Type and I set the Pattern to mm-dd-yyyy, the result ends up displaying as 2018-01-01.  What should my pattern be for the result to appear as 01-01-2018?  Is there a reference somewhere that lists possible patterns for various data types?

[ztruelove]

Looks like no matter what data format I apply, even if I use a predefined Date Style, the result is always the same.  Must be doing something wrong.

The source is a date prompt.  Not sure why it's not wanting to format it.  Playing around with it now...

[dougp]

Hi,

A simpler approach would be to create the calculation expression in the singleton as ?Date?, then use the data format property to format it in whatever way you like.

Cheers!

MF.

Doh!  Of course.  Much simpler.  It was good to practice with macros anyway.

Thanks, all.  Dropping a singleton seems to be the route to go; however, I'm failing on the proper formatting options.  :p

If I go to the Data Format property and choose Date as the Format Type and I set the Pattern to mm-dd-yyyy, the result ends up displaying as 2018-01-01.  What should my pattern be for the result to appear as 01-01-2018?  Is there a reference somewhere that lists possible patterns for various data types?

I created a query calculation:

Code Select Expand

# prompt('date', 'date') # and formatted it as MM-dd-yyyy.  January 18th, 2018 was formatted as 01-18-2018.  If I change the format to mm-dd-yyyy, it's formatted as 00-18-2018 because mm = minutes.

See attached report spec.

[ztruelove]

Well, that was a lot more complicated than I anticipated.  What I ended up having to do was use a Report Expression... string2date (ParamValue('Date'))   ... and then use the appropriate date format pattern.

Thank you all for your suggestions!

[MFGF]

Well, that was a lot more complicated than I anticipated.  What I ended up having to do was use a Report Expression... string2date (ParamValue('Date'))   ... and then use the appropriate date format pattern.

Thank you all for your suggestions!

Did you try my suggestion? Defining the expression in the singleton as ?Date? then using the regular data formatting options works fine for me. I don't have to mess around with functions to convert anything...

MF.

[ztruelove]

I did. Unfortunately, it didn't yield anything other than 2018-01-01 for me, no matter what date formatting option I applied. Maybe it's the version I'm on (10.2.1), or maybe there's some other aspect of my report configuration that I'm not taking into account.

[MFGF]

I did. Unfortunately, it didn't yield anything other than 2018-01-01 for me, no matter what date formatting option I applied. Maybe it's the version I'm on (10.2.1), or maybe there's some other aspect of my report configuration that I'm not taking into account.

Hi,

It's probably down to how your original prompt is defined. Is it a filter based on a date item, using a parameter, eg [Your date item] in_range ?Date?

MF.

[ztruelove]

The package uses a SQL Server stored procedure as the source which has a parameter @Date. I then just created a prompt page with a prompt having the same name (Date). The datatype for the stored procedure is SMALLDATETIME, while the prompt is a date prompt (the version of SQL Server doesn't support the DATE datatype, and the proc doesn't care about a time value anyway). Not sure how much of that is relevant, but I figured I'd share just in case. ;)  But, ultimately, there's no report filter associated with the prompt; it's just acting as a pass-thru to the stored procedure.

[Invisi]

The singleton approach doesn't work for me with this data prompt I have. All I get is this format:

Code Select Expand

2018-01-07T00:00:00.000

[gera]

Thanks, all.  Dropping a singleton seems to be the route to go; however, I'm failing on the proper formatting options.  :p

If I go to the Data Format property and choose Date as the Format Type and I set the Pattern to mm-dd-yyyy, the result ends up displaying as 2018-01-01.  What should my pattern be for the result to appear as 01-01-2018?  Is there a reference somewhere that lists possible patterns for various data types?

Try use <Date Ordering> as <Day, Month, Year> in <Data Format> properties