January 24, 2011 at 4:23 am
I have a database table in sql server where the date is entered via a form on a website. The table field for the date is stored as a varchar (50) data type.
I have created a report using report builder 3.0.
The SQL for my report is as follows:
SELECT
Enquiries.EnquiryID
,Enquiries.CreatedBy
,Enquiries.DateCreated
,Enquiries.OpeningComments
,Enquiries.ClosedBy
,Enquiries.ClosedDate
FROM
Enquiries
WHERE
Enquiries.DateCreated between (@StartDate) AND (@EndDate)
I have created two parameters within the report...@startdate And @endDate.
In my parameter properties i have set the data type to Date/Time as i have only 4 choices of datatype to set it to in Report Builder.
Setting the parameter to date/time datatype enables the user to select a date on a calaneder before the report is run.
Please can you give me some idea of how I can get around the datatype mismatch to enable my report to run. I am unable to change the datatype in the SQL database.
The error I am currently getting is: An error has occoured during report processing
Cannot read the next data row for the dataset EnquiriesbetweenSpecific dates.
The conversion of a varchar data type to a datatime data type resulted in an out of range value.
Thanks
January 24, 2011 at 4:43 am
What format are your Varchar(50) dates in?
January 24, 2011 at 4:55 am
They are stored in the database as:
21/12/2010 00:00:00
January 24, 2011 at 5:05 am
When using convert you need to specify the style. So in your case the convert would look like this:
SELECT CONVERT(DATETIME,'21/12/2010 00:00:00',103)
That will convert your varchar to a date for you. If you want to see what other styles are available see here
Thanks,
Simon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply