I have a report that uses a stored procedure that takes 2 parameters and then returns the result.
exec testcode @startdate, #endate When I run a report from the report server, it runs smoothly without any error message Now when I
exec TestingCode '2012-01-01', '2012-03-01' Then I get the conversion error message.
I know how the error is fine, but passing the parameters from the reporting report does not cause the reporting to fail, but if I execute the stored procedure it fails from the management studio
Thanks
Collaborators
SQL There are several string-based date formats supported by the server - see. Most of those formats are dependent on the settings you make - therefore, these settings may work for some time - and never at all. How to fix this (slightly supported by SQL Server ISO-8601 date format - This format always - Your SQL Server language and dateform settings . Comes in two flavors supported by SQL Server:
-
YYYYMMDD for dates only (no time Not part of here); Note here: No Dash , this is very important! YYYY-MM-DD not will work in all situations! or:
-
YYYY-MM-DDTHH: MM: for the date and time SS - Note here: this format is is dash (but they do may be omitted), and between the date and time part of your DATETIME as a fixed T delimiter. This is valid for SQL Server 2000 and newer.
Up In this case, try this call:
exec TestingCode '20120101', '20120301' Is this work?
If you use SQL Server 2008 or newer and the DATE datatype (only date - not DATETIME !), Then you can actually use the YYYY-MM-DD format and it will work, even with any settings in your SQL server Do not ask me why this whole subject is very difficult and something is misleading - this is the way. But with the YYYYMMDD format, you should be okay for any version of SQL Server and for any language and date format settings in your SQL Server.
No comments:
Post a Comment