Archive for March, 2010

SQL Server Date Format – Convert to varchar

31 March, 2010 Leave a comment

Below is a nice little script to output the various formats displayed by sql server when using convert(varchar, getdate(), n).

It just outputs the current date in the various formats so you can quickly find the one your after

Declare @date datetime
Declare @i int
set @date = getdate()
set @i = 1

while(@i <= 31)
  if(@i not in (15, 16, 17, 18, 19, 22, 23, 24, 25, 28, 29))

  if @i < 22
    print convert(varchar, @i) + ': ' + convert(varchar, @date, @i)

    print convert(varchar, @i+100) + ': ' + convert(varchar, @date, @i+100)

  set @i = @i + 1

Hope it helps

Categories: SQL Server

SQL Server Reporting Services (SSRS) – IIF statement divide by zero error

12 March, 2010 6 comments

If you use the IIF statement to solve your divide by zero errors, you will most likely find that you still get divide by zero errors, as RS evaluates the false condition and creates a divide by zero error.

The following problems is trying to divide last weeks total hours worked by the number of days someone worked, which could be zero.

The follow errors
=IIF(Sum(Fields!LWWorkingDays.Value) = 0, 0,
Sum(Fields!LWTotal.Value)/Sum(Fields!LWWorkingDays.Value) )

The solution:
=IIF(Sum(Fields!LWWorkingDays.Value) = 0, 0,
IIF(Sum(Fields!LWWorkingDays.Value) = 0, 1, Sum(Fields!LWWorkingDays.Value)) / 60)

even though the division is not used, it errors. So by converting the zero to a 1, stops it erroring and allows the calculation to work correctly when it is evaluated.

That is a work around for a problem that should never of existed, good one Microsoft..

SQL Server Reporting Services – Access textbox value in expression

3 March, 2010 Leave a comment

If you need to progamatically access a control on your report then use the following expression