CODEWITHSUNDEEP
sql-server-2008datetimereporting-servicesssrs-2008
Vendoran
Vendoran

Reputation: 176

SSRS 2008 Displaying Date Datetype correctly

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

Upvotes: 0

Views: 1283

Answers (2)

nojetlag
nojetlag

Reputation: 753

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. :(

Upvotes: 0

CodeMonkey1313
CodeMonkey1313

Reputation: 16011

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

Upvotes: 1

Related Questions