Solved

SSRS date format not sorting by year

Posted on 2013-02-07
6
1,706 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

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 we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

746 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

10 Experts available now in Live!

Get 1:1 Help Now