Solved

SSRS date format not sorting by year

Posted on 2013-02-07
6
1,752 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 45

Expert Comment

by:Kdo
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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:Kdo
Kdo 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

3 Use Cases for Connected Systems

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

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

786 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