[sql] How do I format date and time on ssrs report?

on SSRS report I need to show todays date and current time

i tried this =FormatDateTime(Now,"MM/dd/yyyy hh:mm tt") but this is not working for me giving an error.

Anyone please help me for expression ?

I want output display like 4/12/2013 12:05 PM

This question is related to sql reporting-services ssrs-2008

The answer is


=Format(Now(), "MM/dd/yyyy hh:mm tt")

Output:

04/12/2013 05:09 PM

If you click on the empty spot on the report away from any table and then look in properties, one of the Misc fields is called Language which allows you to pick which Language you would like to set, which after doing so can play around with this

=FormatDateTime(now,x)

Which x can be 1, 2, 3, 4, 5


Hope this helps:

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM

SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy – 10/02/2008                  

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – 2008.10.02           

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm

I am using following in SSRS 2005

=Format(Globals!ExecutionTime,"MM-dd-yyyy" & " ") 
& CStr(Hour(Globals!ExecutionTime))  & ":"
& CStr(Minute(Globals!ExecutionTime))

Or

=Format(Globals!ExecutionTime,"MM-dd-yyyy" & " ") 
& Right("00" & CStr(Hour(Globals!ExecutionTime)), 2)
& ":"
& Right("00" & CStr(Minute(Globals!ExecutionTime)), 2)

Based on comment:

=Format(CDate(Globals!ExecutionTime), "MM-dd-yyyy hh:mm.ss") 

OR

=Format(CDate(Globals!ExecutionTime), "MM-dd-yyyy HH:mm.ss")

=Replace(Format(CDate(Now()),"MM.dd.yyyy"), ".", "/")

hi friend please try this expression your report

="Page " + Globals!PageNumber.ToString() + " of " + Globals!OverallTotalPages.ToString() + vbcrlf + "Generated: " + Globals!ExecutionTime.ToString()

The following is how I do it using Visual Studio 2017 for an RDL targetted for SSRS 2017:

Right-click on the field in the textbox on the design surface and choose Placeholder Properties. Choose the Number panel and click on Date in the Category listbox, then select the formatting you are looking for in the Type listbox.


If you want date and time separate then use below expressions: Date and Time Expression

Expression1 for current date : =formatdatetime(today) its return date is = 11/15/2016

Expression2 for current time : =CDate(Now).ToString("hh:mm tt") its return time is = 3:44 PM

This report printed on Expression1 at Expression2

Output will be : Output of Both Expression

This report printed on 11/15/2016 at 3:44 PM


First go to your control panel , select Date , time and Number Format . Now select English(United Kingdom) from the drop down list.

Make sure the shor date field is equal to 'dd/mm/yyyy'. Press Apply. Now go to SSRS and right click on the report in the empty space and select properties.

If you are using visual studio then set Language property equal to =User!Language.

If you are using Report Builder then Language property will appear in Localization section.


You can use as well VB .NET syntax:

=DateTime.Now.ToString("MM/dd/yyyy hh:mm tt")

In SSRS 2016 There is an option under the properties header "Localization" called "Calendar", if you click on this it gives you these 2 options:

  • Gregorian (dd/mm/yyyy)
  • GregorianUSEnglish (MM/dd/yyyy)

This works brilliantly when referencing data from a tables aswell

alternatively if this does not work for you, specify one of these formats under "Number" and in the cell "Format":

dd/MM/yyyy or MM/dd/yyyy

printscreen


If the date and time is in its own cell (aka textbox), then you should look at applying the format to the entire textbox. This will create cleaner exports to other formats; in particular, the value will export as a datetime value to Excel instead of a string.

Use the properties pane or dialog to set the format for the textbox to "MM/dd/yyyy hh:mm tt"

I would only use Ian's answer if the datetime is being concatenated with another string.


I am using this

=Format(Now(), "dd/MM/yyyy hh:mm tt")


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to reporting-services

How to use multiple conditions (With AND) in IIF expressions in ssrs Could not load file or assembly 'Microsoft.ReportViewer.Common, Version=11.0.0.0 Line break in SSRS expression SSRS expression to format two decimal places does not show zeros SSRS Expression for IF, THEN ELSE SSRS Conditional Formatting Switch or IIF SQL variable to hold list of integers Could not load file or assembly 'Microsoft.ReportViewer.WebForms' How do I format date and time on ssrs report? How do I display todays date on SSRS report?

Examples related to ssrs-2008

Line break in SSRS expression SSRS Conditional Formatting Switch or IIF Add column to SQL query results How do I format date and time on ssrs report? How do I display todays date on SSRS report? Cannot create a connection to data source Error (rsErrorOpeningConnection) in SSRS In SSRS, why do I get the error "item with same key has already been added" , when I'm making a new report? How to pass multiple values to single parameter in stored procedure Right pad a string with variable number of spaces SSRS Query execution failed for dataset