February 1, 2010 at 4:18 pm
Hello,
I have a report that takes a start and end date as parameters.
My stored procedure query uses these dates as filters, however the field holding the date is a varchar.
When I run the script in the report in the data tab, the returned data set is how I expected it to be. But running the report using parameters of time datetime does not. I'm assuming that the default datetime format is not mm/dd/yy. So my question is what is it?
AND (cast(t_UserExtendedValue_1.uv_Value as datetime) >= @InvStartDate)
AND (cast(t_UserExtendedValue_1.uv_Value as datetime) <= @InvEndDate)
I've tried to cast the varchar field, as other forum entries suggest, to datetime but get a conversion error:
Conversion failed when converting datetime from character string.
Can anybody help?
thanks,
Paul
February 2, 2010 at 8:08 am
I've tried to cast the varchar field, as other forum entries suggest, to datetime but get a conversion error:
Conversion failed when converting datetime from character string.
This says that you have some data in that varchar column is not a valid date. This is one of the reasons why, if you have the option, you should use correct data types.
One option is to try to clean up the existing data. You can find the rows with invalid data in the date column by using the IsDate() function. It returns 1 for valid dates and 0 for invalid dates.
I hate to say it, but unless the invalid data is fixed you aren't going to get accurate data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 2, 2010 at 8:23 am
Thanks Jack,
This is one of the reasons why, if you have the option, you should use correct data types.
You're preaching to the choir, my friend! I agree entirely but, unfortunately, it's not my call. That field holds many values depending on table joins. So, the option to clean up is not open to me.
However, using ISDATE looks like it may help.
thanks,
Paul
February 2, 2010 at 8:32 am
paucann (2/2/2010)
That field holds many values depending on table joins.
Paul, are you saying that sometimes it holds a date, and other times something else? Jeez, this is a horrible design!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 2, 2010 at 8:59 am
WayneS (2/2/2010)
paucann (2/2/2010)
That field holds many values depending on table joins.Paul, are you saying that sometimes it holds a date, and other times something else? Jeez, this is a horrible design!
I wasn't going to go that far as it is probably some type of EAV design, which I don't think are totally evil, if done right. Done right includes having a data type defined for each attribute in your meta data and verifying the data if the correct data type before inserting or updating the row.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 2, 2010 at 9:50 am
Yes, it's ugly but it's what I have!
Unfortunately, using ISDATE doesn't fix my problem.
AND (t_UserExtendedValue_1.uv_Value >= @InvStartDate)
ISDATE returns an int value to indicate if it's a date or not. At this stage, I already know that 'uv_Value' is a varchar that can only be a date because of the joins I mentioned before (I ran the query with only this field so I know it's only dates returned).
If I pass a value outside of the report, say '01/01/09' to @InvStartDate (and a valid value as my end date) then this works as I expected.
It works when I run it from the Data tab in my report on VS2005, too. It's when I run the report and pass dates as report parameters that the contraints fail. It displays all dates and seems to ignore the contraints. The parameters are of type datetime but clearly there's something different going on.
My stored proc. gets the date params as datetime variables:
@InvStartDate datetime,
@InvEndDate datetime
Casting or converting my varchar field to a date seems superfluous as a) I know it's a date in a varchar field and b) this works outside of the report.
Do you know what I need to do to get it to work from the report params?
thanks,
Paul
February 2, 2010 at 10:04 am
It might help us if you were to post the entire query.
I can't think of any reason why the query would ever work if there is any invalid data in the column as SQL Server is doing an implicit conversion to datetime and that conversion is going to make the query processor apply that conversion to every row.
I suppose the error could be occurring within the report itself. What are you doing with that "date" field in the report?
You could also run Profiler when you run the report to find out exactly what is being passed to the SQL Server when you run the report and then try that in SSMS?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 2, 2010 at 1:48 pm
I don't want to post the whole query but this section prevents anything other than a date stored as a varchar:
t_UserExtended_1.ue_ControlName = 'PO_InvoiceDate'
That's the join I was alluding to when I said it depends what join I use to get certain data. A string of 'PO_InvoiceNo', on the other hand, gets me all the invoice numbers.
The profiler shows the dates coming from the report which has this format:
exec svy_swsc_POLookup @InvStartDate='Feb 3 2010 12:00:00:000AM',@InvEndDate='Feb 16 2010 12:00:00:000AM' . so therein lies my problem.
I do this in my stored proc to translate this date into something I can use in my query:
set @StartDate = CONVERT(datetime, CONVERT(varchar(50), @InvStartDate, 101) )
set @EndDate = CONVERT(datetime, CONVERT(varchar(50), @InvEndDate, 101) )
The intent was to convert the date format of my report params to mm/dd/yy but this isn't working. When I print the variables from the stored proc. it doesn't change:
BEFORE
Jan 1 2010 12:00AM
Mar 1 2010 12:00AM
AFTER
Jan 1 2010 12:00AM
Mar 1 2010 12:00AM
I think if I can change this format i.e. (t_UserExtendedValue_1.uv_Value >= '01/01/09') then it'll work.
Does anyone know why my conversion doesn't work?
thanks,
Paul
February 2, 2010 at 2:18 pm
paucann (2/2/2010)
I don't want to post the whole query but this section prevents anything other than a date stored as a varchar:t_UserExtended_1.ue_ControlName = 'PO_InvoiceDate'
That's the join I was alluding to when I said it depends what join I use to get certain data. A string of 'PO_InvoiceNo', on the other hand, gets me all the invoice numbers.
This does not mean that you can't have invalid date data in the column. Depending on your Language and DateFormat settings this date 30/11/2009 may or may not be valid. Since the data type is not enforced using one of the Date data types any text value could be stored even if the ue_ControlName value is 'PO_InvoiceDate' so you could still get the conversion error.
The profiler shows the dates coming from the report which has this format:
exec svy_swsc_POLookup @InvStartDate='Feb 3 2010 12:00:00:000AM',@InvEndDate='Feb 16 2010 12:00:00:000AM' .so therein lies my problem.
This isn't a problem as SQL Server because you said earlier that your stored procedure parameters are defined as datetime and internally SQL Server stores datetime data as integers.
I do this in my stored proc to translate this date into something I can use in my query:
set @StartDate = CONVERT(datetime, CONVERT(varchar(50), @InvStartDate, 101) )set @EndDate = CONVERT(datetime, CONVERT(varchar(50), @InvEndDate, 101) )
The intent was to convert the date format of my report params to mm/dd/yy but this isn't working. When I print the variables from the stored proc. it doesn't change:
BEFORE
Jan 1 2010 12:00AM
Mar 1 2010 12:00AM
AFTER
Jan 1 2010 12:00AM
Mar 1 2010 12:00AM
This is because you are working with datetime values and that is just how SQL Server displays it when using the print function. If you do Select @StartDate you'll get 2010-01-01 00:00:00.000.
I think if I can change this format i.e. (t_UserExtendedValue_1.uv_Value >= '01/01/09') then it'll work.
Does anyone know why my conversion doesn't work?
thanks,
Paul
The Convert IS working. You are converting a datetime to a varchar back to a datetime. I wouldn't be surprised if the Query Processor was totally ignoring your conversion because internally SQL Server ends up with the same integer value.
What you want, and I don't think you'll get the results you expect, is just convert the datetime parameters to varchar, then SQL Server will do string comparison NOT date comparison meaning that you need to be sure that your values are ALWAYS MM/DD/YYYY or else you will definitely get inconsistent results. When doing date comparison '01/01/2010' is between '1/1/2010' and '2/1/2010' but not when you are doing string comparison. Here's an example:
SELECT
CASE
WHEN '01/01/2010' BETWEEN '1/01/2010' AND '2/1/2010' THEN 'In January'
ELSE 'Not in January'
END AS string_comparison,
CASE
WHEN CONVERT(DATETIME, '01/01/2010') BETWEEN '1/01/2010' AND '2/1/2010' THEN 'In January'
ELSE 'Not in January'
END AS date_comparison
I would run this query against my source data:
SELECT
*
FROM
t_UserExtended_1 AS UE JOIN
t_UserExtendedValue_1 UEV ON
UE.primary_key = UEV.foreign_key
WHERE
t_UserExtended_1.ue_ControlName = 'PO_InvoiceDate' AND
ISDATE(t_UserExtendedValue_1.VALUE) = 0
And I'd be willing to bet you have some values that need to be corrected.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 2, 2010 at 2:36 pm
Could you possibly post some of the date values as stored in the table?
Maybe you're struggling with issues related to the way the values are stored:
Example:
If data would be stored using e.g. German style, then you'd end up with '23.01.2010' for January 23rd. SQL Server won't like to deal with that format though...
But as Jack recommended: run his query to figure out which values you have with 'PO_InvoiceDate' but still cannot be converted to a date format.
And I'd second his will to bet to have invalid data...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply