SSRS 2008 Displaying Date Datetype correctly

Question

I'm using SQL 2008 and SSRS 2008:

CREATE TABLE [dbo].[DimDates](
    [pkDateID] [int] NOT NULL,
    [FullDate] [date] NULL,
    [DayNumberOfWeek] [tinyint] NULL,
    [DayNameOfWeek] [varchar](9) NULL,
    [IsLastDayOfMonth] [char](1) NULL,
    [MonthName] [varchar](9) NULL,
    [CalendarDay] [smallint] NULL,
    [CalendarWeek] [tinyint] NULL,
    [CalendarMonth] [tinyint] NULL,
    [CalendarQuarter] [tinyint] NULL,
    [CalendarYear] [smallint] NULL,
    [FiscalDay] [smallint] NULL,
    [FiscalWeek] [tinyint] NULL,
    [FiscalMonth] [tinyint] NULL,
    [FiscalQuarter] [tinyint] NULL,
    [FiscalYear] [smallint] NULL,
)
GO

CREATE PROCEDURE [dbo].[LookupWeek]
    @StartDateID int = NULL
AS
    IF @StartDateID IS NULL
     SELECT
      dd.pkDateID
      , dd.FullDate
     FROM
      dbo.DimDates dd
     WHERE
      dd.DayNumberOfWeek = 7
      AND dd.FullDate <= GETDATE()
     ORDER BY
      dd.pkDateID DESC
    ELSE
     SELECT
      dd.pkDateID
      , dd.FullDate
     FROM
      dbo.DimDates dd
     WHERE
      dd.DayNumberOfWeek = 7
      AND dd.pkDateID >= @StartDateID
      AND dd.FullDate <= GETDATE()
     ORDER BY
      dd.pkDateID DESC
RETURN 0
GO

We're utilizing the sp above to fill both the @StartDate and @EndDate in a SSRS report. The issue occurs when displaying FullDate in the drop down box for the parameter, it doesn't display as a date, it displays as a datetime and adds the 12:00:00 AM to every row.

Any ideas why or how we can stop it?

Thanks,

--Aaron

Answer 1

because its a datetime object, you'll either need to do a split on the field (Split(field, " ")(0)) or use DateFormat and provide the desired format string (=FormatDateTime(today, dateformat.shortdate))

That is designed to be in the report, not the sql.

Or set the format property on the textbox to "d" which indicates short datetime

Answer 2

I feel this is a bug. I'm also frustrated at this. In the database the column is clearly defined as "DATE" and when you create a dataset within SSRS it is represented as "DATETIME". This is complete bull.... Why can't they respect their own data types between their own products. I don't wanna make any fancy reformating for something that was defined by its type within the database.

I was hoping they fix it in R2 but, dream on. :(