locked
Date format "MM/DD/YYYY" not sorting correctly RRS feed

  • Question

  • Created a custom SSRS report. applied an interactive sort for a date field. I have the the formatting set to "MM/DD/YYYY" and it sorts everything correctly except for the year.

    Example:

    As you can see, the dates with 2014 aren't sorted correctly. I want it to sort the dates like:

    11/06/2014

    12/10/2014

    01/07/2015 etc, etc.

    Is there a way to get this to take into account the year? but also the month and day?

    Thanks,


    Monday, August 17, 2015 6:20 PM

Answers

  • Just use 

    =CDate(Fields!QA_Deployment_Date.Value)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by AUtfsAdmin Thursday, August 20, 2015 1:08 PM
    Wednesday, August 19, 2015 2:14 PM

All replies

  • Hi AutfsAdmin,

    Try this

    In SQL server query use 

    convert

     

    (datetime, Datecolumn)

    And at SSRS level use this.
    you can try the following:
    =Format(Fields!myDate.Value, “MM/dd/yyyy”)

    This thread is something like yours

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6573b9f7-8ebc-400a-9f5f-ad20b54d9cdc/interactive-sort-for-the-date-column-in-ssrs-2005


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    Monday, August 17, 2015 6:31 PM
  • Hi AutfsAdmin,

    Try this

    In SQL server query use 

    convert

     

    (datetime, Datecolumn)

    And at SSRS level use this.
    you can try the following:
    =Format(Fields!myDate.Value, “MM/dd/yyyy”)

    This thread is something like yours

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6573b9f7-8ebc-400a-9f5f-ad20b54d9cdc/interactive-sort-for-the-date-column-in-ssrs-2005


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    Monday, August 17, 2015 6:32 PM
  • Hi AutfsAdmin,

    Try this

    In SQL server query use 

    convert

     

    (datetime, Datecolumn)

    And at SSRS level use this.
    you can try the following:
    =Format(Fields!myDate.Value, “MM/dd/yyyy”)

    This thread is something like yours

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6573b9f7-8ebc-400a-9f5f-ad20b54d9cdc/interactive-sort-for-the-date-column-in-ssrs-2005


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    I build the report with Report Builder 3.0 from TFS data. How can I add "convert (date, Datecolumn)" . I am already using =FormatCDate(Fields!myDate.Value), "MM/dd/yyyy") on the field
    Monday, August 17, 2015 7:00 PM
  • You'll want to make sure the date is returned as a datetime object from your initial query, and only formatted in SSRS/Report Builder.

    If you cannot do that, ensure the sort is done on an expression which converts it back, CDATE, for example:

    =CDATE(Fields!myDate.Value)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    • Edited by Patrick Hurst Monday, August 17, 2015 7:27 PM
    • Proposed as answer by Naomi N Monday, August 17, 2015 7:38 PM
    Monday, August 17, 2015 7:26 PM
  • You'll want to make sure the date is returned as a datetime object from your initial query, and only formatted in SSRS/Report Builder.

    If you cannot do that, ensure the sort is done on an expression which converts it back, CDATE, for example:

    =CDATE(Fields!myDate.Value)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    Thanks for your response but as you can see from my last post.. I am already using CDATE in my expression.

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

    rHow can I "make sure the date is returned as a datetime object from my initial query"? Can this be done in Query Designer? 


    Tuesday, August 18, 2015 11:42 AM
  • How the dates are stored in the table and what is your query? Make sure you're just returning the column if it's date/datetime and not formatting it in the query itself.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, August 18, 2015 12:07 PM
  • How the dates are stored in the table and what is your query? Make sure you're just returning the column if it's date/datetime and not formatting it in the query itself.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    The date is from a field in TFS. The field type is "DATE TIME"

    without any formatting, the date is returned in the report like:

    It doesn't sort correctly. But If I add the expression of:

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

    It displays:

    The only thing wrong is it doesn't take into account the year. The 2014 years are at the end since the months are 11 and 12 which come last. I just want to make my expression take into account the year as well as month and day. That is all I am trying to accomplish.

    Can I just modify my expression above to make it look at the year as well? If so, how?

    Thanks!

    Tuesday, August 18, 2015 5:53 PM
  • Hi AutfsAdmin,

    Check this out.

    you can add multiple sorting fields and thier order Sort by year first and then month.
    =Year(Fields!QA_Deployment_Date.Value)
    =Month(Fields!QA_Deployment_Date.Value)


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    Tuesday, August 18, 2015 6:06 PM
  • Just use 

    =CDate(Fields!QA_Deployment_Date.Value)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by AUtfsAdmin Thursday, August 20, 2015 1:08 PM
    Wednesday, August 19, 2015 2:14 PM
  • Just use 

    =CDate(Fields!QA_Deployment_Date.Value)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    That actually seems to work. I have it setup like the following:

    QA Deployment Date column header sorted Detail rows and sort by expression "=CDate(Fields!QA_Deployment_Date.Value)"

    The first text box below the QA Deployment Date header.. I have it set to an expression and use:

    "=CDate(Fields!QA_Deployment_Date.Value)"

    The results are:

    The only problem I have with it now is the time.. Can I add anything to the expression to remove the time?

    Edit... I modified the expression on the text box under the QA Deployment Date header to be:

    =FormatDateTime(Fields!QA_Deployment_Date.Value,2)

    I did leave the Header sorting set to the expression:

    =CDate(Fields!QA_Deployment_Date.Value).

    Thanks for all the help everyone!

    • Edited by AUtfsAdmin Thursday, August 20, 2015 1:08 PM edit
    Thursday, August 20, 2015 12:50 PM
  • Apparently your interactive sort treats your sorting field as if it was a text.

    Within the Interative Sort tab, have it sorted by this expression =Format(CDate(Fields!Your_Date_Field.Value), "yyyy-MM-dd")

    The CDate might not be required in case Your_Date_Field comes out of the query as datetime data type.

    Thursday, August 20, 2015 1:18 PM