asked
how to set a variable to create a view with a date condition
I'm trying to query a table and save the result on a view. My SP has three variables and one of them it's a date field. when i use the date in an AND condition i get an error message 'Conversion failed when converting date and/or time from character string.' how can i fix this. SET @qry2 ='CREATE View [dbo].[MarketData] as SELECT * FROM [BReport].[dbo].[table_'+ @ID+'] where cid = '''+@CID+''' and DATEDIFF(d,[Result Date/Time],DATEADD(dd,0,'+@Date+')) = 0' EXEC (@qry2) i'm want to get result where date = 1 day old
Any variable that is not a string needs to be converted to one before concatenating to a string. In your case, you have 3. Based on the names, I'm guessing the first 2 are integers. If so ... CONVERT(varchar(20), @ID) CONVERT(varchar(20), @CID) The date is a little more complicated (and I'm confused by the DATEADD of 0 days). This is what I think you need for your SET statement: SET @qry2 = 'CREATE VIEW [dbo].[MarketData] AS SELECT * FROM [BReport].[dbo].[table_' + CONVERT(varchar(20), @ID) + '] WHERE cid = ' + CONVERT(varchar(20), @CID) + ' AND DATEDIFF(d, [Result Date/Time], CONVERT(datetime2(0), ''' + CONVERT(varchar(50), @date, 120) + ''', 120)) = 1 ;' ;
I decided to limit my answer strictly to what you were asking, but I can't help wondering what the purpose of this is. Your stored procedure creates hard-coded views? Could a table-valued function work for your needs? It can take parameters rather than hard-coding them in a series of views.
Thanks for your help that did the work. i dont really need to create the view, that was an attempt to try to solve the date issue i have. i'm creating a report with dynamic columns, report always have to have previous date data, data is coming from multiple tables, it then generates the customer details. i then unpivot the data and presented on a report. i can remove the view and put the select statement on my unpivot query to generate the report. Thanks
Glad I could help. Please mark the answer as correct so others know your problem is solved.
18 People are following this question.
