Reporting Services Remove Time from DateTime in Expression

Question

I'm trying to populate an expression (default value of a parameter) with an explicit time. How do I remove the time from the the "now" function?

Check out Method 1: http://stackoverflow.com/a/19693553/2635532 — Anup Agrawal, Feb 05 '16 at 17:17
It is important to note that even if the SQL within returns a Date variable only, the rdl will still use DateTime. Therefore, it must be formatted by the rdl -- see various answers below. — JosephDoggie, Jan 15 '20 at 14:08

score 110 · Answer 1 · edited Apr 27 '12 at 05:46

110

Something like this:

=FormatDateTime(Now, DateFormat.ShortDate)

Where "Now" can be replaced by the name of the date/time field that you're trying to convert.)
For instance,

=FormatDateTime(Fields!StartDate.Value, DateFormat.ShortDate)

edited Apr 27 '12 at 05:46

Sathyajith Bhat

21,321
22
95
134

answered Sep 08 '09 at 18:21

Michael Maddox

12,331
5
38
40

6

As my date parameter was a datetime, I required an additional CDate() around the above expression. – Nick.Mc Mar 13 '14 at 01:51
1

Won't work if the report is required in Excel format. FormatDateTIme is string function. Sure it strips off the time, but the result is a string date column which when sorting will be sorted as a string from left to right. The user would have to convert the column to a real Date using Text to Columns. – Fandango68 Jun 21 '16 at 00:17
1

The point is, you cannot do what the OP asked in Date only format. You're stuck with string dates. – Fandango68 Jun 21 '16 at 00:20
This gave me exactly what I needed. I had a DOB column that SSRS assumed needed to add a time to. Microsoft really needs to update this software. – Jan 28 '21 at 17:23

score 46 · Answer 2 · answered Sep 08 '09 at 18:32

46

Since SSRS utilizes VB, you can do the following:

=Today() 'returns date only

If you were to use:

=Now() 'returns date and current timestamp

answered Sep 08 '09 at 18:32

RSolberg

26,821
23
116
160

9

=Today() does not return date only in SSRS – donviti Jul 29 '14 at 16:00
1

To clarify, `Today()` returns the date only in terms of value, but the datatype returned is `DateTime`. – Chris Mack Nov 14 '18 at 16:00

score 29 · Answer 3 · edited Feb 04 '16 at 12:12

29

=CDate(Now).ToString("dd/MM/yyyy")

Although you are hardcoding the date formart to a locale.

edited Feb 04 '16 at 12:12

Pedram

6,256
10
65
87

answered Jan 19 '10 at 17:18

Perhentian

860
8
12

2

+1 for the concise answer that, as a matter of fact, was very helpful to me. – jasonco Jan 20 '10 at 10:52
1

As I mentioned above, this works but turns the column into a string. It's still not a true DATE only datatype. – Fandango68 Jun 21 '16 at 00:19

score 23 · Answer 4 · answered Nov 20 '15 at 09:31

23

If you have to display the field on report header then try this... RightClick on Textbox > Properties > Category > date > select *Format (Note this will maintain the regional settings).

Since this question has been viewed many times, I'm posting it... Hope it helps.

answered Nov 20 '15 at 09:31

singhswat

832
7
20

1

What if it gets ignored for whatever reason? E.g. date is still shown with hours and minutes. Any idea why this could be happening? – Alex Mar 03 '16 at 10:43
2

If you have a function specified for the value of the TextBox/Placeholder then it will override the formatting options on this screen. – Wayne May 31 '16 at 20:04
1

Saved my day. Event though it is easily visible in the options many wont look here for formatting option. Thanks a lot. – Rohith Jan 30 '18 at 09:03

score 8 · Answer 5 · edited Apr 13 '12 at 12:23

8

Just use DateValue(Now) if you want the result to be of DateTime data type.

edited Apr 13 '12 at 12:23

antyrat

27,479
9
75
76

answered Apr 13 '12 at 08:35

user1165019

93
2
5

score 8 · Answer 6 · answered Feb 04 '16 at 12:17

If expected data format is MM-dd-yyyy then try below,

=CDate(Now).ToString("MM-dd-yyyy")

Similarly you can try this one,

=Format(Today(),"MM-dd-yyyy")

Output: 02-04-2016

Note:
Now() will show you current date and time stamp

Today() will show you Date only not time part.

Also you can set any date format instead of MM-dd-yyyy in my example.

score 5 · Answer 7 · answered Sep 09 '09 at 16:02

5

In the format property of any textbox field you can use format strings:

e.g. D/M/Y, D, etc.

answered Sep 09 '09 at 16:02

zzawaideh

2,021
1
17
25

4

The question is referring to the default value of a report parameter, so setting the format on a textbox won't work. Also, "D/M/Y" won't give the expected result, try "dd/MM/yyyy" instead. – Valentino Vranken Jul 31 '12 at 08:24

score 3 · Answer 8 · edited Feb 27 '13 at 16:02

3

One thing that might help others is that you can place: =CDate(Now).ToString("dd/MM/yyyy") in the Format String Property of SSRS which can be obtained by right clicking the column. That is the cleanest way to do it. Then your expression won't be too large and difficult to visually "parse" :)

edited Feb 27 '13 at 16:02

Hasturkun

35,395
6
71
104

answered Feb 27 '13 at 15:43

lbranjord

31
1

score 2 · Accepted Answer · edited Feb 04 '16 at 12:11

2

Found the solution from here

This gets the last second of the previous day:

DateAdd("s",-1,DateAdd("d",1,Today())

This returns the last second of the previous week:

=dateadd("d", -Weekday(Now), (DateAdd("s",-1,DateAdd("d",1,Today()))))

edited Feb 04 '16 at 12:11

Pedram

6,256
10
65
87

answered Sep 08 '09 at 18:22

Jeff

8,020
34
99
157

7

I stumbled here from google.. but.. this doesn't answer your question. (why do you care about the last second?) However, you did beat RSolberg (who correctly answered it). Maybe you should edit your accepted answer to reference his correct answer? – itchi Aug 26 '10 at 00:29
3

DateAdd("s",-1,DateAdd("d",1,Today()) returns the last second of Today, not previous day - and there's a closing backet missing to make it work – Valentino Vranken Jul 31 '12 at 08:20

score 2 · Answer 10 · answered Jan 11 '13 at 18:57

2

    FormatDateTime(Parameter.StartDate.Value)

answered Jan 11 '13 at 18:57

Alivia

1,312
1
10
17

1

You are missing the second parameter of the expression FormatDateTime(DateField, DateFormat) – ShellNinja Jan 23 '14 at 13:31

Chris Wood · Answer 11 · 2014-06-24T14:12:31.103

I'm coming late in the game but I tried all of the solutions above! couldn't get it to drop the zero's in the parameter and give me a default (it ignored the formatting or appeared blank). I was using SSRS 2005 so was struggling with its clunky / buggy issues.

My workaround was to add a column to the custom [DimDate] table in my database that I was pulling dates from. I added a column that was a string representation in the desired format of the [date] column. I then created 2 new Datasets in SSRS that pulled in the following queries for 2 defaults for my 'To' & 'From' date defaults -

'from'

    SELECT  Datestring
    FROM    dbo.dimDate
    WHERE   [date] = ( SELECT   MAX(date)
                       FROM     dbo.dimdate
                       WHERE    date < DATEADD(month, -3, GETDATE()
                     )

'to'

    SELECT  Datestring
    FROM    dbo.dimDate
    WHERE   [date] = ( SELECT   MAX(date)
                       FROM     dbo.dimdate
                       WHERE    date <= GETDATE()
                     )

score 2 · Answer 12 · edited Feb 04 '16 at 12:12

2

This should be done in the dataset. You could do this

Select CAST(CAST(YourDateTime as date) AS Varchar(11)) as DateColumnName

In SSRS Layout, just do this =Fields!DateColumnName.Value

edited Feb 04 '16 at 12:12

Pedram

6,256
10
65
87

answered Jul 30 '15 at 22:00

Ricky Hope

51
1
5

Won't strip off the time component is the DateColumnName is a DateTime datatype. The OP wants the date only. – Fandango68 Jun 21 '16 at 00:13

score 2 · Answer 13 · answered Sep 18 '15 at 01:14

2

My solution for a Date/Time parameter:

=CDate(Today())

The trick is to convert back to a DateTime as recommend Perhentian.

answered Sep 18 '15 at 01:14

will webster

574
5
11

score 2 · Answer 14 · edited Jul 14 '17 at 01:15

2

Just concatenate a string to the end of the value:

Fields!<your field>.Value & " " 'test'

and this should work!

edited Jul 14 '17 at 01:15

Nathan Tuggy

2,237
27
30
38

answered Jul 14 '17 at 00:44

amad

59
1
4

1

Hi Nathan, Would you please elaborate on your editing ?. sorry i am kind of new to stackoverflow. Regards! – amad Jul 14 '17 at 15:33

Reporting Services Remove Time from DateTime in Expression

14 Answers14