April 13, 2017 at 9:10 am
Hi,
is it possible to convert a date time parameter to a string?
I have parameters @StartDate and @EndDate which are DateTime in SSRS and when filtering a view by date time it's giving me incorrect data I have converted the datetime to a string in the view and when I use SQL to filter the report on that it gives me the correct data
The current Query looks like this....
SELECT
QuartzReportnew.SALES_PERSON
,QuartzReportnew.ACCOUNT_NO
,QuartzReportnew.Group_Name
,QuartzReportnew.ACCOUNT_NAME
,QuartzReportnew.[MONTH]
,QuartzReportnew.MONTH_NUMBER
,QuartzReportnew.Year_Month
,QuartzReportnew.ORDERDATE
,QuartzReportnew.ORDERDATESTRING
,QuartzReportnew.TYPE
,QuartzReportnew.[YEAR]
,QuartzReportnew.SP
,QuartzReportnew.WO
,QuartzReportnew.NP8
,QuartzReportnew.SO
FROM
QuartzReportnew
WHERE
QuartzReportnew.ORDERDATE >= @StartDate
AND
QuartzReportnew.ORDERDATE < DateAdd(dd,1,@EndDate)
AND
QuartzReportnew.Group_Name IN (@GroupName)
AND
QuartzReportnew.TYPE IN (@Type)
AND
QuartzReportnew.SALES_PERSON IN (@SalesPerson)
What I would like to do is something along the lines of .........
WHERE
QuartzReportnew.ORDERDATESTRING = CONVERT(DATETIME,@StartDate,103)
AND
QuartzReportnew.ORDERDATESTRING = CONVERT(DATETIME,@EndDate,103)
But that gives me an error.....Any ideas how I can do this please?
Thanks in advance.
Paul.
April 13, 2017 at 9:22 am
The problem sounds more like that you're storing your datetime in your table as a varchar, rather than a datetime. Is my guess correct?
Also "gives you an error"; What error?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 13, 2017 at 9:30 am
Hi Thom,
the error I get is
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
----------------------------
Cannot read the next data row for the dataset DataSet1. (rsErrorReadingNextDataRow)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)
In my view I have ORDERDATEANDTIME stored as a datetime and I also have a column called ORDERDATESTRING which is ORDERDATEANDTIME converted to a string with the code
CONVERT(Varchar(10), ORD.ORDERDATEANDTIME, 103)
The problem I have is when I filter the report on ORDERDATEANDTIME when testing it in SQL I get a different result to when I filter the same report by ORDERDATESTRING for some unknown reason but its the ORDERDATESTRING filter that appears to give the correct results so I am trying to filter by that in SSRS but to do that I need to convert the date time parameters to a string first somehow.
April 13, 2017 at 9:37 am
Could you post some sample data which shows the problem (CREATE, INSERT, SELECT statements). Also, I can't see the column ORDERDATEANDTIME in your original query, only ORDERDATE?
On a different note, doing date logic on a varchar isn't a great idea. It'll be slow, and, more than likely, give unexpected results. Yes, you're saying that the results you're getting with the varchar are the expected results, but they may not hold true in the future. You should use dates for dates, numerics for numerics, strings for strings. Strings as a replacement for any of these is generally frowned upon at best.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 13, 2017 at 9:40 am
103 is dd/mm/yyyy, which is an ambiguous date format. Could it be that your report server is set up to interpret dates as mm/dd/yyyy?
John
April 13, 2017 at 9:47 am
Yes, sorry...ORDERDATE not ORDERDATETIME.
Not sure how to post sample data....
These are the 2 queries I run in SQL on the views
select * from QuartzReportnew where ORDERDATE = convert(datetime,'12/04/2017',103)
select * from QuartzReportnew where ORDERDATESTRING = '12/04/2017'
The first select statement gives me only 41 rows but the second returns 81 rows. I have no idea why this is, surely they are both the same query?
April 13, 2017 at 9:50 am
John Mitchell-245523 - Thursday, April 13, 2017 9:40 AM103 is dd/mm/yyyy, which is an ambiguous date format. Could it be that your report server is set up to interpret dates as mm/dd/yyyy?John
Hi John,
Where would I find that setting in SSRS?
Thank you
Paul.
April 13, 2017 at 9:53 am
Do the values in ORDERDATE have a time portion? If so, that'll explain the discrepancy, since in the ORDERDATESTRING column, you chop that portion off.
Where would I find that setting in SSRS?
I don't know, but if Windows is installed with a US locale, there's a good chance that's how it'll interpret dates. As Thom said, store dates in date columns, and don't mix. And when you pass in a date value, do so in an unambiguous format, such as '20170412 16:46:34.265'
John
April 13, 2017 at 10:29 am
OMG, don't use strings. SSRS parameters have 2 properties: value and label. Value should be a date/time data type, while label can be a string representation of that value and should be left assigned to the local setting of each user.
The Stored procedure or query should receive the parameters as a date/time data type and should be kept that way to compare against columns with the same data type. Don't store dates in string format, they are more complicated to handle and validate, and they use more storage/memory.
April 13, 2017 at 11:04 am
I'm with Luis, one of the most recurring performance and bad data issues i see is not maintaining data types when comparing data to parameters.
datetimes are datetimes, varchars are varchars, nvarchars are their own separate animal too.. don't mix and match.
Lowell
April 18, 2017 at 7:01 am
Thank you all for your replies.
I know what is happening now
If I filter on datetime like so....
select orderdate,orderdatestring from QuartzReportnew where ORDERDATE = convert(datetime,'12/04/2017',103)
It only returns jobs where the orderdate = 2017-04-12 00:00:00.000. If there is any time in the time section it gets ignored in the query where as if I filter by the string like so....
select orderdate,ORDERDATESTRING from QuartzReportnew where ORDERDATESTRING = '12/04/2017'
I get all the data back even if there is data in the time field.
Does anyone know how I can get the filter to work correctly, i.e. filter on datetime rather than filter on the string?
April 18, 2017 at 7:22 am
select orderdate from QuartzReportnew where orderdate >= '12/04/2017' AND orderdate < '12/05/2017';
April 18, 2017 at 7:40 am
paul 69259 - Tuesday, April 18, 2017 7:01 AMThank you all for your replies.I know what is happening now
If I filter on datetime like so....
select orderdate,orderdatestring from QuartzReportnew where ORDERDATE = convert(datetime,'12/04/2017',103)It only returns jobs where the orderdate = 2017-04-12 00:00:00.000. If there is any time in the time section it gets ignored in the query where as if I filter by the string like so....
select orderdate,ORDERDATESTRING from QuartzReportnew where ORDERDATESTRING = '12/04/2017'I get all the data back even if there is data in the time field.
Does anyone know how I can get the filter to work correctly, i.e. filter on datetime rather than filter on the string?
A couple of answers below, however, first i feel it best to explain why this is happening.
At the moment (in simple terms) you are trying to return values in a table where the value of a datetime column is less than or equal to a specific date. Let's say that the date you are supplying is today (18 April 2017). When you compare this date to a datetime column, the date parameter will be effectively be read as a datetime. This means that 18 April 2017 becomes 18 April 2017 00:00:00.000. If you are storing times as well, anything that occurs after the stroke of midnight won't be counted. For example 18 April 2017 00:00:00.003 is AFTER the value of your parameter.
The two answers below are different, but will give the same result (if using a date parameter type). The first converts your datetime values to a date. So 18 April 2017 17:15:24.123 would be read as 18 April 2017, which would be equal to or less than your parameter. The second adds a day to your parameter, however, excludes times that are equal to the paramter's value. Thus it returns all values that have a datetime value of less than 19 April 2017 00:00:00.000. So this would include datetimes up to and including 18 April 2017 23:59:997 if datetime, or 18 April 2017 23:59:99.9~ if datetime2(x).
SELECT *
FROM Mytable
WHERE CAST(MyDateColumn AS date) = @DateParameter;
SELECT *
FROM Mytable
WHERE MyDateColumn < DATEADD(DAY, 1, @DateParameter);
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 18, 2017 at 8:26 am
Thank you Thom for this explanation.
You have been a great help.
Paul.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply