SQLTeam.com | Weblogs | Forums

Ssrs DATE PARAMETERS ISSUES


#1

I have two dates i am using in my ssrs reports . start_date & end_date
both dates are time stamped so before i bring it over to my reports table i do a
convert on both convert(date, start_date) whch updates my dates from 10-05-2015 .00.00.00 to
10-05-2015. however when i import that date into SSRS dataset and do a distinct on all the dates my SSRS filter start_date
reverts back to 10-05-2015 .00.00.00 i dont want it in that formart it should be in mm-dd-yyyy.
can i format my dates within SSRS filter to mm-dd-yyyy


#2

try this expression :-

=Format(Field!start_date.value,"dd-mm-yyyy")


#3

You should follow ISO-8601 rules for displaying temporal data This is the only format allowed in ANSI/ISO Standard SQL and the other ISO standards. It is also why we database people make fun of you guys who speak in a hillbilly date dialect (https://xkcd.com/1179/).

Also, never use the old Sybase CONVERT() string function. We put it in the early Sybase SQL products to help COBOL programmers whose language lacked temporal data types and had to use strings. The concept of a tiered architecture was brand new back them, but this is 2016!


#4

You can use STR_TO_DATE() to convert your strings to MySQL date values and ORDER BY the result:

ORDER BY STR_TO_DATE(datestring, '%d/%m/%Y')

However, you would be wise to convert the column to the DATE data type instead of using strings.