Format Date Expression in Text Box to omit the Time SQL Server 2005

Question

Post reply

Format Date Expression in Text Box to omit the Time SQL Server 2005

Welsh Corgi

SSC Guru

Points: 116520
More actions
November 18, 2011 at 5:23 am

#247158

I need to Format Date Expression in Text Box to omit the Time SQL Server 2005
In SSRS 2008 you simply right click on the text box, select properties and select the date format that you want.
Any help would be greatly appreciated.
=DateAdd("d", -(WeekDay(Today(),2))+1, Today())
Displays the last day of last week.
11/12/2011 12:00:00:00 AM
How can I format it to omit the time?
Edited: I found where to the format the text box. It is not as obvious as in 2008.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Welsh Corgi (11/18/2011)
Edited: I found where to the format the text box. It is not as obvious as in 2008.

That's why they make new versions, to improve things 🙂

Can you share your solution, so that if a visitor lands on this page he can benefit from your result?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I wish that we did not have SQL Server 2005 for Reporting Services.

I would have preferred to be able to format the date in a text box that is concatenated with text.

I had to create a separate text box and enter the express to calculate the last day of last week.

THe right click on the text box and select properties.

Click on the format tab.

To the right of the format code column, click on the ellipsis, click on date and then click on the date format that you want.

Does anyone know how I can alter the format of the following to omit the time? Datepart perhaps?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 3

Welsh Corgi (11/18/2011)
I need to Format Date Expression in Text Box to omit the Time SQL Server 2005

The letter d will format the date without the time.

Similarly, to format a number with 2 decimal places you can use N2.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

WILLIAM MITCHELL (11/23/2011)
Welsh Corgi (11/18/2011)
I need to Format Date Expression in Text Box to omit the Time SQL Server 2005
The letter d will format the date without the time.
Similarly, to format a number with 2 decimal places you can use N2.

So how would I alter my syntax?

=DateAdd("d", -(WeekDay(Today(),2))+1, Today())

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 5

The syntax stays the same, it is only the format code that has to change. Two ways to get there:

#1 right-click on the text box, click properties to bring up the properties dialog box, click the Format tab, observe the "Format code" box and click the builder (...) and you can view the choices for the various formats. Select the one that looks like the current date, without the time.

#2 on the fly-out properties panel at the right of the screen, under the Appearance group, find the Format prop and enter the letter d

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

I'm sorry, I misunderstood.

I found how to format the text box but I want to include the date in a text box that has other text items before and after and I do not want to use a seperate text box for the date.

:unsure:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 7

ok, understood.

=FormatDateTime(DateAdd("d", -(WeekDay(Today(),2))+1, Today()),DateFormat.ShortDate)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Thanks, that was easy.:cool:

I was not aware of that function.

I goggled and I could not find anything.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/