CODEWITHSUNDEEP
sql-serverreporting-servicesparameters
diiN__________
diiN__________

Reputation: 7666

SQL Report Builder: Return only date for available parameter values

I have a SQL Server report which prompts for a import date. Later I pass this date to a procedure to get the data (dbo.spGetData). My procedure to get the available import dates looks like this:

ALTER PROCEDURE dbo.spGetImportDates
AS
BEGIN
    SELECT CONVERT(DATE, i.ImportDate) ImportDate
    FROM dbo.Import i
    ORDER BY i.ImportDate DESC
END

In the report I've added this SP as a new dataset and selected it as "Available Values" for the Date/Time parameter @importDate to get a dropdown. So far, so good. The problem is that the time is also included in the result set when I leave it like this:

+---------------------+
| ImportDate          |
+---------------------+
| 29.03.2017 00:00:00 |
| 25.03.2017 00:00:00 |
| ...                 |
+---------------------+

I couldn't find any option to change the format of the parameter prompt. I even tried to use the SELECT query directly in the query designer of the dataset but I get the same results.

Do I really have to use it as text parameter and then reconvert it to DATE in the procedure dbo.spGetData or is there any other solution?

Upvotes: 1

Views: 2817

Answers (2)

Cookie Monster
Cookie Monster

Reputation: 475

Leave the ImportDate column in the stored procedure as DATE datatype. Go to your dataset in SSRS and click on 'Fields'. Add a new calculated field, name it ImportDateLabel and set it as the following expression:

=Format(Fields!ImportDate.Value,"dd/mm/yyyy")

Now go to your Paramater, click on 'Available values' and change the 'Label field' to reference the new ImportDateLabel field. Leave the 'Value field' pointing to the ImportDate field.

Upvotes: 3

beejm
beejm

Reputation: 2481

ALTER PROCEDURE dbo.spGetImportDates
AS
BEGIN
    SELECT CAST(CONVERT(DATE, i.ImportDate) AS NVARCHAR(10)) AS ImportDate
    FROM dbo.Import i
    ORDER BY i.ImportDate DESC
END

You can get only the first 10 characters and then convert it to varchar

Upvotes: 0

Related Questions