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

CodeMonkey1313 CodeMonkey1313 · Accepted Answer · 2009-06-16T16:05:18

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

SSRS 2008 Displaying Date Datetype correctly

2 Answers