How do I format date and time on ssrs report?

Question

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

score 275 · Accepted Answer · edited Oct 21 '22 at 05:21

275

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

Output:

04/12/2013 05:09 PM

Full list of format options might be found here. Kudos to @MattGibson.

edited Oct 21 '22 at 05:21

Heavy JS

51
7

answered Apr 12 '13 at 16:10

Ian Preston

38,816
8
95
92

12

Possible `Format()` strings are described in this article: [Date and Time Format Strings](https://msdn.microsoft.com/en-us/library/97x6twsz.aspx); I figured I'd mention that as it was what I was looking for when I arrived here! – Matt Gibson Jan 20 '16 at 10:05

Jamie F · Answer 2 · 2015-04-13T13:39:01.467

22

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.

edited Apr 13 '15 at 13:39

answered Apr 12 '13 at 17:39

Jamie F

23,189
5
61
77

4

Little addon for people that may find this answer: You set the format to `="MM/dd/yyyy hh:mm tt"`. If you forget the `=`, every cell will just have `"MM/dd/yyyy hh:mm tt"` as text. – user1261104 Sep 08 '16 at 08:42

score 8 · Answer 3 · edited Apr 11 '18 at 05:14

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

Formatting should never be done in the datbase. It creates all kind of problems (sorting, filtering etc.) It should only be done on the presentation layer (in this case, in the report). This solution is not recommended. — swissmawi, Dec 12 '22 at 08:58

LCJ · Answer 4 · 2022-12-09T18:13:48.533

7

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)

OR:

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

OR

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

edited Dec 09 '22 at 18:13

answered Apr 23 '14 at 19:14

LCJ

22,196
67
260
418

4

Both can look simpler: `=Format(CDate(Globals!ExecutionTime), "MM-dd-yyyy hh:mm.ss")` or `=Format(CDate(Globals!ExecutionTime), "MM-dd-yyyy HH:mm.ss")` – desperate man Dec 30 '14 at 14:10

score 6 · Answer 5 · edited Oct 16 '19 at 16:28

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

score 2 · Answer 6 · answered Jun 28 '18 at 15:55

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.

score 1 · Answer 7 · answered Aug 22 '16 at 17:11

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

Laxman Gite · Answer 8 · 2016-11-15T10:28:13.590

1

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

edited Nov 15 '16 at 10:28

answered Nov 15 '16 at 10:22

Laxman Gite

2,248
2
15
23

Thanks, I tried using Expression2, but it's showing 03:44 PM. Is there a way to make it show 3:44 PM and drop the leading 0? – Helen Neely Jul 06 '22 at 14:42

score 1 · Answer 9 · edited Nov 14 '17 at 08:34

1

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

edited Nov 14 '17 at 08:34

Abdul Rasheed

6,486
4
32
48

answered Nov 14 '17 at 07:47

Ronney

11
1

score 1 · Answer 10 · answered Apr 03 '19 at 11:48

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.

score -4 · Answer 11 · edited Mar 03 '14 at 20:29

-4

hi friend please try this expression your report

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

edited Mar 03 '14 at 20:29

Taryn

242,637
56
362
405

answered Mar 03 '14 at 14:29

venkataramana

29

Folks, this does answer the question. The date is at the far right end of the expression. The extra info is useful too. Deserves more up votes imo. – Jeff May 26 '22 at 23:13

How do I format date and time on ssrs report?

11 Answers11

Linked

Related