Format Datetime Parameter Label
I'm trying to format the label of a parameter which is populated by a dropdown in SQL2008 RS. Essentially, i want to put a title in the report which puts the date in the format dd MMM yyyy. I have tried the following options with no luck: =Format(Parameters!MyDate.Label, "dd MMM yyyy") =Format(CDate(Parameters!MyDate.Label), "dd MMM yyyy") Can this be achieved in this way? I "could" reformat the data in the dataset, but i'd rather explore this avenue first. TIAevery day is a school day
September 9th, 2010 1:27pm

Is there any particular reason why you're using the parameter label? Try: =Format(Parameters!MyDate.Value, "dd MMM yyyy")
Free Windows Admin Tool Kit Click here and download it now
September 9th, 2010 2:31pm

I need to use the Label rather than the value as the value is an integer and the display is a date.every day is a school day
September 9th, 2010 2:53pm

Can you explain a bit more please? Are you creating the label from an expression; what's the expression? What is the value of the field if it's not the date/time?
Free Windows Admin Tool Kit Click here and download it now
September 9th, 2010 4:22pm

Hi, Did the parameter come from one dataset? If so, you can insert a calculated field for this dataset with the expression =Format(Fields!MyDate.value,"dd MMM yyyy"), then this calculated feid can be used as the labels. Please let me know if you have more question. thanks, Jerry
September 10th, 2010 8:48am

Hi guys- thanks for the responses. I have the following dataset for my parameter. CREATE TABLE dbo.DropDownDataSet (Id INT, Dt DATETIME) INSERT INTO dbo.DropDownDataSet SELECT 1 AS Id, '20100630' AS Dt INSERT INTO dbo.DropDownDataSet SELECT 2 AS Id, '20100731' AS Dt SELECT * FROM dbo.DropDownDataSet My Parameter value is Id and the label is Dt. I am happy with how these are being displayed. However, i want a formatted version of Dt in my report but am unable to get this working. every day is a school day
Free Windows Admin Tool Kit Click here and download it now
September 10th, 2010 11:52am

You have a parameter value with two labels; Unusual but not illegal as far as I'm aware! What I'd suggest trying is creating a new parameter holding just the date (as it's value) and then using that to set the value of your existing parameter, i.e. use: SELECT * FROMdbo.DropDownDataSet WHERE Dt = [@NewParameter] as the default value and set the parameter as 'hidden'. You can then use the value of your new parameter in your original expressions in place of the label. There may be simpler solutions.
September 10th, 2010 4:15pm

Try this: =CDate(Parameters!MyDate.Label).ToString("dd MMM yyyy")
Free Windows Admin Tool Kit Click here and download it now
September 10th, 2010 9:45pm

I think you need to have a delimiter to identify it as a Date like 2010-11-13 or 2010/11/13. If you try to convert 20101113 to date using CDate it may throw error. If you can get the data as DateTime from database you can directly you the Format(). If you can get it only as label (i mean text only), , then you can try something like this: <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman";} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> = CDate( Left(Parameters!TestDate.Value, 4) + "-" + Mid(Parameters!TestDate.Value, 5, 2) + "-" + Right(Parameters!TestDate.Value, 2)).ToString("dd MMM yyyy" ) if you can get it with a delimiter as - or / then you can directly use Format, if not try the above option that works fine. Swamy
September 11th, 2010 1:09am

Try this one, it should work. (small correction, used Label instead of Value. = CDate( Left(Parameters!MyDate.Label, 4) + "-" + Mid(Parameters!MyDate.Label, 5, 2) + "-" + Right(Parameters!MyDate.Label, 2)).ToString("dd MMM yyyy") Swamy
Free Windows Admin Tool Kit Click here and download it now
September 11th, 2010 1:13am

=format(Fields!date.Value, "dd/MM/yyyy") works pucker for me!!
December 9th, 2010 4:46am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics