none
SSRS 2008 R2 parameter date/time - how to hide time?

    Question

  • Using a date/time parameter, I am selecting a query (stored procedure) to display available values. The prompted values include the date and time shown as 12:00:00 AM. I would like to not display the time and only show the date. How can I hide or remove the time as we do not store times? Many Thanks

    Thursday, May 26, 2011 7:03 PM

Answers

  • Hi patrcik,

    Mate, you  need to format your field in SSRS 2008 development studio prior to show in the table, no need t format in the query. use the following format expression to exclude the timestamp from the field and show only the date part of the field:

    =Format(Fields!Start_Date.Value,"dd/MM/yyyy")

    hope this helps.


    | Khurram Jamshed | Follow my blog about Enterprise Project Management Solution | http://khurramjamshed.blogspot.com |
    • Proposed as answer by Jeevan Dasari Tuesday, May 31, 2011 5:22 AM
    • Marked as answer by PatrickSql Friday, June 03, 2011 5:07 AM
    Sunday, May 29, 2011 8:28 AM
  • This will work for formatting a text box, however none of this will work to get the question answered.

    If I have a dropdown parameter that gets populated from a query, how do I get the time from appearing on the front end?

    The way to do this, is to add a formatted string column to the dataset and show the formatted string and use the datetime value as the value.

    -Edward


    Edward
    Tuesday, October 18, 2011 1:03 AM
  • Hi PatrickSql,

    Thanks for your question. According to your description, I understand that you want to display the date/Time type parameter only show date part not including hh:mm:ss part. Khurram’s solution of Format(Parameters!Timer.Value,"dd/MM/yyyy") has powerful effect, you should format it in SSRS develop studio not in the query, thanks Khurram. You can also use FormatDateTime(Parameters!Timer.Value, DateFormat.ShortDate) or FormatDateTime(Parameters!Timer.Value,2) to achieve your target, for detail steps, please follow below,

    Supposing you have a date/Time parameter @Timer,

    1. Open your report in SQL Server Business Intelligence Development Studio or Report Builder3.0.

    2. Click the Design tab, right-click the textbox where you will display the @Time parameter, select expression.

    3. Clear the expression dialog box, then type in: 
    =FormatDateTime(Parameters!Timer.Value, DateFormat.ShortDate) or =FormatDateTime(Parameters!Timer.Value,2).

    Please preview your report, you will see the result you want.

    This is the similar thread I had handled about Passing Date parameter to a subreport, please see: http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/fef8b560-11e1-420d-a915-40ffcb6d632d.

    If you have any question, please feel free to let me know.

    Thanks,
    Sharp Wang


    • Proposed as answer by Jeevan Dasari Tuesday, May 31, 2011 5:22 AM
    • Marked as answer by PatrickSql Friday, June 03, 2011 5:07 AM
    Tuesday, May 31, 2011 3:21 AM
    Moderator

All replies

  • Use this in the SELECT statement.

    SELECT CONVERT(char(10), DateColumn, 101) 

    Thursday, May 26, 2011 7:32 PM
  • Shahfaisal, thank you for your reply. This does not work since after converting to char now the date/time parameter no longer matches.
    PH
    Friday, May 27, 2011 4:16 PM
  • If you are using SQL Server 2008, use

    CAST(DateColumn AS DATE)

    Friday, May 27, 2011 4:19 PM
  • Hi patrcik,

    Mate, you  need to format your field in SSRS 2008 development studio prior to show in the table, no need t format in the query. use the following format expression to exclude the timestamp from the field and show only the date part of the field:

    =Format(Fields!Start_Date.Value,"dd/MM/yyyy")

    hope this helps.


    | Khurram Jamshed | Follow my blog about Enterprise Project Management Solution | http://khurramjamshed.blogspot.com |
    • Proposed as answer by Jeevan Dasari Tuesday, May 31, 2011 5:22 AM
    • Marked as answer by PatrickSql Friday, June 03, 2011 5:07 AM
    Sunday, May 29, 2011 8:28 AM
  • Hi PatrickSql,

    Thanks for your question. According to your description, I understand that you want to display the date/Time type parameter only show date part not including hh:mm:ss part. Khurram’s solution of Format(Parameters!Timer.Value,"dd/MM/yyyy") has powerful effect, you should format it in SSRS develop studio not in the query, thanks Khurram. You can also use FormatDateTime(Parameters!Timer.Value, DateFormat.ShortDate) or FormatDateTime(Parameters!Timer.Value,2) to achieve your target, for detail steps, please follow below,

    Supposing you have a date/Time parameter @Timer,

    1. Open your report in SQL Server Business Intelligence Development Studio or Report Builder3.0.

    2. Click the Design tab, right-click the textbox where you will display the @Time parameter, select expression.

    3. Clear the expression dialog box, then type in: 
    =FormatDateTime(Parameters!Timer.Value, DateFormat.ShortDate) or =FormatDateTime(Parameters!Timer.Value,2).

    Please preview your report, you will see the result you want.

    This is the similar thread I had handled about Passing Date parameter to a subreport, please see: http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/fef8b560-11e1-420d-a915-40ffcb6d632d.

    If you have any question, please feel free to let me know.

    Thanks,
    Sharp Wang


    • Proposed as answer by Jeevan Dasari Tuesday, May 31, 2011 5:22 AM
    • Marked as answer by PatrickSql Friday, June 03, 2011 5:07 AM
    Tuesday, May 31, 2011 3:21 AM
    Moderator
  • Thank you - that worked
    PH
    Friday, June 03, 2011 5:07 AM
  • Thank you for your help
    PH
    Friday, June 03, 2011 5:08 AM
  • This will work for formatting a text box, however none of this will work to get the question answered.

    If I have a dropdown parameter that gets populated from a query, how do I get the time from appearing on the front end?

    The way to do this, is to add a formatted string column to the dataset and show the formatted string and use the datetime value as the value.

    -Edward


    Edward
    Tuesday, October 18, 2011 1:03 AM
  • We were working on one of the reports where we have to use Date values(FROM sql ) as Parameters and then comes the issue with Time Stamp which have to be removed. Here are the steps adding to emeshuris steps.

    1.For Dataset : Add 1 extra  field to SQL query for start date and add +1 for any other date parameters . 

    use : Convert(varchar(30),Startdate,101) as StartDatep  (Dont worry for casting back to date as this is only used as label in SSRS)

    In BIDS use StartDatep as the Label and StartDate as Value . This creates a mapping between the label and value fields as these values are being driven from a Dataset.

    this approach worked for me and can save some Dev time.


    Rahul Parimal Sharepoint , Project server

    • Proposed as answer by Rahul Parimal Wednesday, February 29, 2012 7:47 PM
    Wednesday, February 29, 2012 7:46 PM
  • Did you actually find a solution? I have the same problem. I noticed one thing:

    I start the report, the parameter (calendarcontrol) is shown with current date & time (06-09-2012 09:01)

    When I delete the timepart manually and then I use the calendercontrol to select a different date, it no longer shows the time portion....

    Using FormatDateTime() on the default parameter however does not work, the report claims there is no valid default value for the parameter.

    I will keep on sdearching. If you might already have found the solution, I would very muvch like to hear how you solved it!

    Thursday, September 06, 2012 7:03 AM
  • We solved it together!!  I took your half-solution and with one more step it works.  You just need to convert the time-less date back to a datetime and it takes the default and does not show the time.

    Add the following as the default value for the datetime parameter and the time will not show.  You can, of course, replace Now() with any other set of functions/literals that produce a datetime.

    =CDate(FormatDateTime(Now(),DateFormat.ShortDate))

    Yay!!

    -Ken


    • Edited by Ken in Tampa Tuesday, October 08, 2013 4:12 PM
    • Proposed as answer by Ken in Tampa Tuesday, October 08, 2013 4:12 PM
    Tuesday, October 08, 2013 4:11 PM