Solved

SSRS date format not sorting by year

Posted on 2013-02-07
6
1,781 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: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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSIS I need to get the most old file from a folder in the SSIS package. 3 39
Webservices in T-SQL 3 33
Query Syntax 17 43
Substring works but need to tweak it 14 12
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

839 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