In SQL Server Reporting Services what have many date parameters declared as DateTime and so they display a pure date with the time of "12:00 AM". We want the DateTime data type for validation purposes. Is there a way to force the format of the display to only show the date? I have tried various approaches with formatting but they all produce execution errors. I would be happy with using a String if I can validate the data to make sure it is a date.
Thanks for your help.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Thanks for your answer but it is displaying the dates in a combobox for the user to choose; not quite what I need.
In my situation the date entry is free form and entered into a text box. The problem is the user enters, say, "11/07/2005" and when the report executes Reporting Services displays the entered date at "11/07/2005 12:00 AM". I want to NOT display the "12:00 AM".
If your referring to Report Manager displaying the DateTime in the parameter input box, then no, you cannot format this. You can only format the date within the report itself, or like the example I posted before, give the user a dropdown parameter list of dates.
Apparently you can set the parameter to a string and then it won't enter the time, though you'll have to convert it into a date before it runs against your dataset. This way though, report users can enter a non-date as a parameter.
That would be fantastic information to have. If you can refer me to any Microsoft documentation that discusses this or if you happen to have any notes, I would be very grateful. I have been looking for this for about a year. I have been validating in the Stored procedure, but it would be nice to add it to my DLL and reference the functions and not have to copy and past in the SP. Thanks a bunch
Business Accounts
Answer for Membership
by: simon_kirkPosted on 2005-11-07 at 01:58:45ID: 15237663
Hi,
This example assumes your date fields have no specific time, i.e. they are all 12:00AM.
For some of my reports I create a parameter dataset based on the table holding the dates.
SELECT DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField) AS Label, MyDateField AS Value
FROM MyTable
GROUP BY DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField), MyDateField
ORDER BY MyDateField
This creates 2 fields, 'Label' which is what the user selects from and 'Value' which the dataset uses to query on.
Create a parameter (@MyParameterDate) and reference it against your main dataset e.g.
SELECT * FROM MyTable WHERE MyDateField = @MyParameterDate
In your Report parameters set the values to be from a query and select your parameter dataset. Set the Value and Label datafields to the ones created above and ensure the parameter datatype is DateTime. You can change the above format if you don't want dates displaying as '7 November 2005'.
HTH