Solved

SSRS date format not sorting by year

Posted on 2013-02-07
6
1,734 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - Sums/Grouping 7 51
all records from previous month 6 45
Query Peformance + mulitple query plans 9 47
MS SQL - Rotating Values in SQL 9 50
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now