none
Format Datetime Parameter Label

    Question

  • 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.

    TIA


    every day is a school day
    Thursday, September 09, 2010 10:27 AM

Answers

  • 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

    Friday, September 10, 2010 10:13 PM

All replies

  • Is there any particular reason why you're using the parameter label? Try:

    =Format(Parameters!MyDate.Value, "dd MMM yyyy")

    Thursday, September 09, 2010 11:31 AM
  • 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
    Thursday, September 09, 2010 11:53 AM
  • 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?
    Thursday, September 09, 2010 1:22 PM
  • 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

    Friday, September 10, 2010 5:48 AM
    Moderator
  • 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
    • Edited by richbrownesq Friday, September 10, 2010 1:21 PM Rapier spotted my typo! Thanks!
    Friday, September 10, 2010 8:52 AM
  • 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.

    Friday, September 10, 2010 1:15 PM
  • Try this:

    =CDate(Parameters!MyDate.Label).ToString("dd MMM yyyy")

    Friday, September 10, 2010 6:45 PM
  • 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

    Friday, September 10, 2010 10:09 PM
  • 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

    Friday, September 10, 2010 10:13 PM
  • =format(Fields!date.Value,

    "dd/MM/yyyy")

    works pucker for me!!

     

    Thursday, December 09, 2010 9:45 AM