Need date parameter conversion

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Need date parameter conversion

Author

Topic

Tart_SQL
Starting Member

41 Posts

Posted - 2008-08-22 : 18:20:48

I have a datetime report parameter which is set to todays date, I need it to display the last day of current month in a textbox. What is the formula for getting this? Any help is appreciated.

PS.

@date should give me Aug 31, '08
and in another textbox 31 AUG 2008.
So I need two formulas, thanks.

ma.voice
Starting Member

12 Posts

Posted - 2008-08-22 : 19:41:17

Hi, Try the following example and change it according to your requirement.

DECLARE @pInputDate datetime
DECLARE @vOutputDate DATETIME

SET @pInputDate = '02-22-2008'
SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01'
SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))
select convert(varchar(12),@vOutputDate,100)
select convert(varchar(12),@vOutputDate,106)

Cheers

Silent Voice
Bill Gates, MVP

ma.voice
Starting Member

12 Posts

Posted - 2008-08-22 : 19:44:15

Its a TSQL fix.

Cheers

Silent Voice
Bill Gates, MVP

Tart_SQL
Starting Member

41 Posts

Posted - 2008-08-22 : 19:51:05

Sorry I should have mentioned earlier, I want the VB.Net code to use with the report.

Something Like CDate(Now).ToString....
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-23 : 01:25:42

=DateAdd("d",(-1)*(DatePart("d",Now())-1),DateAdd("m",1,Parameters!YourDateParam.value))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-23 : 01:26:55

And ofcourse, for formatting you can right click textbox and select formatting tab and select one of standard dateformats (which is available by clicking ... button)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-23 : 03:38:59

quote:
Originally posted by ma.voice

Its a TSQL fix.

Cheers

Silent Voice
Bill Gates, MVP

Why do you want to do this in tsql while the formation is easy in reporting services?

Madhivanan

Failing to plan is Planning to fail

Tart_SQL
Starting Member

41 Posts

Posted - 2008-08-23 : 13:22:17

quote:
Originally posted by visakh16

=DateAdd("d",(-1)*(DatePart("d",Now())-1),DateAdd("m",1,Parameters!YourDateParam.value))

Hi Visak,

The above code gives me the first day of the month, what I need is the last day of the current month. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-24 : 14:05:29

what about this?
=DateAdd("d",(-1)*DatePart("d",Now()),DateAdd("m",1,Now()))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources