August 27, 2013 at 3:06 pm
I have a table with two fields: Fromdate and todate that I change every month to the current month value.
For example this month fromdate is '2013-08-01' and todate is '2013-08-31' Then when I run the program from a job, it will alread have the date range in the selection process.
This is what I do in Teradata which works:
DECLARE @tempfromdate AS varchar(10)
DECLARE @fromdate AS varchar(10)
DECLARE @temptodate AS varchar(10)
DECLARE @todate AS varchar(10)
select @tempfromdate = fromdate,
@temptodate = todate
from tbl_fiscper
set @fromdate = CONVERT(VARCHAR(10), @tempfromdate, 111)
set @todate = CONVERT(VARCHAR(10), @temptodate, 111)
DECLARE @sql VARCHAR(MAX)
SET @sql = '
SELECT *
FROM OPENQUERY (TERADATA,
''
select *
from teradata table
where reportdate between between ''''' + @fromdate + ''''' and ''''' + @todate + '''''
'')
'
EXEC (@SQL)
***EDIT***
sorry, I should have added this to the post.
This is what works in Oracle with a hard coded dates:
where to_char(RPT_DATE,''''yyyy-mm-dd'''') between ''''2013-01-01'''' and ''''2013-08-31''''
I can't get the parameter to work.
Any thoughts?
Thanks
August 27, 2013 at 4:18 pm
You convert to format 111 which is YYYY/MM/DD, and then you tell Oracle to convert to YYYY-MM-DD, and then it goes downhill from there.
Try format 121 instead.
And if Oracle is like SQL Server, I think you should convert the date column to character since you are killing the index. (But maybe Oracle is smart, who knows.)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 28, 2013 at 7:15 am
Thank you for pointing out my error. I corrected it and still no luck. Since this is a relatively small table, I selected the whole year via hard coded date to a temp table and then selecting the date range from the temp table. I wanted to do it at the Oracle level since we have tables in Oracle that I need to use and there will be a lot more rows to pull from. Thanks again.
August 28, 2013 at 9:02 am
What does "no luck" mean? Error message? Unexpected results?
And if you add PRINT @sql what do you see?
But if the table is small, why not filter on the SQL Server side? Or use four-part notation, and no need to struggle with dynamic SQL.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply