Formatting DateTime with milliseconds and retaining regional settings

IH 21 Reputation points
2022-09-22T17:59:09.157+00:00

How do I display milliseconds in a datetime and retain local regional settings e.g.

2022-09-21 13:56:07.999
9/21/2022 1:56:07.999 PM
21. 9.2022 1.56.07.999 PM

etc.

I would like to use this as an expression in SSRS Report Builder.

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.
3,065 questions
{count} votes

Accepted answer
  1. Anonymous
    2022-09-23T07:31:53.717+00:00

    Hi @IH
    In SSRS, the now function can return a date value containing the current date and time according to the system. If you want to keep the locale setting, it's as simple as setting the Language expression to =User!Language in the report properties pane.
    244119-1.png
    But using an expression in the report builder to keep the locale setting seems to be difficult to do to display the milliseconds of the date. SSRS does not display the milliseconds of the time by default. If you want to display it, using =FORMAT(NOW(), "MM/dd/yyyy hh:mm:ss.fff tt") seems to be a convenient method, but the time format in the local area will be destroyed.
    What I can do is to separate the date from the time, the date is in the regional format, and the time is judged according to the value returned by the now function to determine whether there is "AM", "PM" in it, to choose different formats to display milliseconds.

    =FormatDateTime(Now(), DateFormat.ShortDate)&" "&IIF(TimeOfDay().tostring.Contains("PM"),FORMAT(NOW(), "h:mm:ss.fff tt"),  
    iif(TimeOfDay().tostring.Contains("AM"),FORMAT(NOW(), "h:mm:ss.fff tt"),  
    FORMAT(NOW(), "HH:mm:ss.fff")))  
    

    Preview:
    244212-2.png
    Hope this can help you.
    Best regards,
    Aniya

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Michael Taylor 60,331 Reputation points
    2022-09-22T20:58:56.823+00:00

    That's an interesting problem since you don't necessarily know where the time is. What I would probably do is get the current culture's DateTimeFormatInfo. Within here is the FullDateTimePattern which contains the format string that will be used. However it is just a concat of LongDatePattern and LongTimePattern so I'd probably take LongTimePattern, append the millisecond specifier to the time (by searching for the s letters, and then use that to format the value. Honestly how much this is doable directly in an SSRS expression is limited. Therefore I'd probably put this in a function that SSRS calls.

    Perhaps something like this (untested):

       Public Function MyFormatDateTime ( ByVal value as DateTime) As String  
          Dim culture = CultureInfo.CurrentCulture  
         
          Dim format = culture.LongTimePattern  
          Dim index = format.LastIndexOf("s")  
          If index >= 0 Then  
             format = format.Insert(index, "fff")  
          End If  
         
          Return value.ToString(culture.LongDatePattern) & " " & value.ToString(format)  
       End Function  
    
    0 comments No comments

  2. Joyzhao-MSFT 15,636 Reputation points
    2022-09-23T01:54:53.25+00:00

    Hi @IH ,
    You would need to FORMAT the field with the little known fff for the milliseconds. You would also need to use tt for the AM or PM.

    You can use the FORMAT function to format it:

    =FORMAT(NOW(), "MM/dd/yyyy hh:mm:ss.fff tt")  
    

    Or you can use the FORMAT property of the text box.

    243977-01.png

    Preview:

    244045-02.png
    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.


  3. IH 21 Reputation points
    2022-09-26T15:16:58.12+00:00

    None of the suggestions would work. In the end, my solution was to just search the string and replace AM or PM with ".fff 'AM'" or ".fff 'PM'". This seems to work, but not ideal.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.