Date entered as 1/1/2001 is showing on the report as 00/01/2001

Dwight 1 Reputation point
2022-02-26T22:08:33.497+00:00

I've got a parameter for my SSRS report where the person will enter a date to pull back all data after that date. I want to be able to show that date in the report header so people know how the report was run.

When I enter something like 1/1/2001, or even 10/1/2001, it displays on the report as 00/01/2001.

Here is the code for formatting the report header field:

="Project ECP CAO_DueDate >= " + Format(Parameters!begindate.Value , "mm/dd/yyyy")

I'm pretty sure I had the same problem trying to use Cast instead of Format.

As a parameter to use for filtering, it is working. Seems to be anyway.

Any ideas?

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,824 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Dwight 1 Reputation point
    2022-02-26T22:29:25.657+00:00

    It wanted "MM/dd/yyyy" for the Format parameter. WTH?

    0 comments No comments

  2. Joyzhao-MSFT 15,566 Reputation points
    2022-02-28T02:46:24.403+00:00

    Hi @Dwight ,
    It's because mm is for minutes, not months. See more: Class SimpleDateFormat.

    In C# and VB.NET, it's a case sensitive especially to format the Month or Minutes. Because initial character of both the words is same.

    The following detail may help you in formatting the date & time.

    1. d/D: day without 0 prefix on single digit.   
    2. dd/DD: day with 0 prefix if single digit.  
    3. M: Month without 0 prefix on single digit.  
    4. MM: Month with 0 prefix if single digit.  
    5. yy/YY: Last two digit of year.  
    6. yyyy/YYYY: represents full year.  
    7. HH: 24 hour based hour. ie. 13:00. **H** will display hour without prefix 0 if single digit  
    8. hh: 12 hour based hour. ie. 01:00. **h** will display hour without prefix 0 if single digit  
    9. m: minute without 0 prefix on single digit.   
    10.mm: minute with 0 prefix if single digit.  
    11.s: second without 0 prefix on single digit.   
    12.ss: second with 0 prefix if single digit.  
    13.tt: represent the AM or PM  
    

    Best Regards,
    Joy


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. Olaf Helper 41,331 Reputation points
    2022-02-28T09:33:29.637+00:00

    Format(Parameters!begindate.Value , "mm/dd/yyyy")

    Format parameters are case sensitive and lower mm is for minutes, while upper MM if for month.

    See https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings => "mm" / "MM"

    0 comments No comments