Solved

SSRS date format not sorting by year

Posted on 2013-02-07
6
1,813 Views
Last Modified: 2013-02-07
I have a report in SSRS with a Date column.  The value of the date column is the field "Expected" which is what I'm grouping on.  Some example values are...
01/03/2013 8:00AM
01/18/2013 8:00AM
09/04/2013 8:00AM
When I run the report it appears to be in order by month and day but not year.  I've attached a picture of the report to  show expressions and groupings.  I could use some help on getting the date in order by year 1st then month and day.
Rpt.jpg
0
Comment
Question by:BobRosas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 38865347
Hi Bob,

I suspect that your "date" column is actually a varchar() column that contains a formatted date.

If so, just recast the column back to a datetime or timestamp in the query.


Good Luck,
Kent
0
 
LVL 6

Expert Comment

by:liija
ID: 38865364
Strange. Looks like a bug, perhaps the sort with Format()-function doesn't work correctly.

Quick 'n' dirty fix: add new field 'Year' where you have the year only. Sort first by Year-field (which doesn't have to be in the table), then by Fields!Expected.Value
0
 

Author Comment

by:BobRosas
ID: 38865436
Thank you for your quick response.  Would you show me an expression that changes a date to year?  I tried
=Format(Fields!Expected.Value, "y")
=Format(Fields!Expected.Value, "yy")
=Format(Fields!Expected.Value, "yyyy")
They all compile and run but the result is just "y"s

=Formatdatetime(Fields!Expected.Value, "y")
complies but shows error in field
=Formatdatetime(Fields!Expected.Value, "yy")
does not compile
=Formatdatetime(Fields!Expected.Value, "yyyy")
compiles but shows error in field
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 6

Accepted Solution

by:
liija earned 150 total points
ID: 38865447
Try
=Datepart("yyyy", Fields!Expected.value)

Is this field already a date?
0
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 150 total points
ID: 38865456
Easy enough.  :)

  select cast ('01/03/2013 8:00AM' as datetime);

Just replace the string with your column name.


Kent
0
 

Author Comment

by:BobRosas
ID: 38865577
Thank you both for your help.  Something very strange is going on.  When i finally got the year field formatted it was still in order with 2013 1st and 2012 2nd.  So I checked group properties for the field and the order shows A to Z.  So I changed it to Z to A and now 2012 is first and 1013 is 2nd.  That does not make sense but the data looks correct.  I will increase points and divide because once I have more time I plan to try and fix my dataset per Kdo's suggestion.  It's a complicated stored procedure but the format is probably the issue.  Thank you both!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question