Date format parameter from Oracle to SQL

Hello SQL expert,

Below is my test simple query and I need to change the date on the where clause to as a date parameter. Anyone know how to do it?

I am using CTE to query of Oracle data using OPENQUERY. The date range are currently hard coded but I want to be able to do it as a parameter.

;with Localdata1 as
(
SELECT BusDriverID
,BusDriverName
,routeID
FROM OPENQUERY (ServerName1, 'SELECT BusDriverID
,BusDriverName
FROM SchemaName1.Transportation_tbl
WHERE trunc(dr_time) between to_date(''01/01/2014'', ''mm/dd/yyyy'') and to_date(''03/31/2014'',''mm/dd/yyyy'') '
) a
LEFT JOIN OPENQUERY(ServerName2,'SELECT BusDriverID
,routeID
,RouteName
FROM SchemaName2.Roadmap_tbl
WHERE State in (''CA'')') b
on a.BusDriverID = b.BusDriverID

--Finaloutput
SELECT * FROM Localdata1

OPENQUERY does not accept variables for its arguments so you have to do the qhole thing as Dynamic SQL :frowning: (Max Query parameter for OPENQUERY is 8K)

Because of the hassle of doubled-up-quotes I do the thing in multiple stages, and I use PRINT to preview the actual SQL until I get something that works (i.e. running it manually as a test) and only then do I use the EXECUTE command

Something like this:

DECLARE	@strSQL nvarchar(4000),
	@strSQL1 nvarchar(4000),
	@strSQL2 nvarchar(4000),
	@strStartDate nvarchar(10),
	@strEndDate nvarchar(10)
--
SELECT	@strStartDate = '01/01/2014',
	@strEndDate = '03/31/2014'
--
SELECT	@strSQL1 = 
'SELECT	BusDriverID, BusDriverName 
FROM	Transportation_tbl 
WHERE	    trunc(dr_time) between to_date(''' + @strStartDate + ''', ''mm/dd/yyyy'') 
	and to_date(''' + @strEndDate + ''',''mm/dd/yyyy'')',
	@strSQL2 = 
'SELECT	BusDriverID, routeID, RouteName
FROM	Roadmap_tbl 
WHERE	State in (''CA'')'
--
PRINT	'@strSQL1=' + @strSQL1
PRINT ''
PRINT	'@strSQL2=' + @strSQL2
PRINT ''
--
SELECT	@strSQL =
';with Localdata1 as
(
SELECT 
FROM OPENQUERY (TransServer1, ''' + REPLACE(@strSQL1, '''', '''''') + '''
) a
LEFT JOIN OPENQUERY(TransServer2,''' + REPLACE(@strSQL2, '''', '''''') + ''') b
on a.BusDriverID = b.BusDriverID
-- Finaloutput
SELECT * FROM Localdata1'
--
PRINT '@strSQL=' + @strSQL

Once you are sure you have the correct SQL then d

EXEC (@strSQL)

to execute it

Hi Kristen,

Thank you. I am going to try and test it....I will let you know the end result. Thank you again.

Hello Kristen,

I am getting incorrect syntax near the keyword FROM. Question, do you need to place * (start) after the SELECT below statement inside the CTE?

SELECT @strSQL =
';with Localdata1 as
(
SELECT
FROM OPENQUERY (TransServer1, ''' + REPLACE(@strSQL1, '''', '''''') + '''
) a
LEFT JOIN OPENQUERY(TransServer2,''' + REPLACE(@strSQL2, '''', '''''') + ''') b
on a.BusDriverID = b.BusDriverID
strong text

Sorry, looks like a Cut&Paste error somewhere along the way. I think this should be the complete SQL - when I run it here it (now) generates identical SQL to your original query (apart from formatting which I fiddled with a bit)

DECLARE	@strSQL nvarchar(4000),
	@strSQL1 nvarchar(4000),
	@strSQL2 nvarchar(4000),
	@strStartDate nvarchar(10),
	@strEndDate nvarchar(10)
--
SELECT	@strStartDate = '01/01/2014',
	@strEndDate = '03/31/2014'
--
SELECT	@strSQL1 = 
'SELECT	  BusDriverID
	, BusDriverName 
FROM	Transportation_tbl 
WHERE	    trunc(dr_time) between to_date(''' + @strStartDate + ''', ''mm/dd/yyyy'') 
	and to_date(''' + @strEndDate + ''',''mm/dd/yyyy'')',
	@strSQL2 = 
'SELECT	  BusDriverID
	, routeID
	, RouteName
FROM	Roadmap_tbl 
WHERE	State in (''CA'')'
--
PRINT	'@strSQL1=' + @strSQL1
PRINT ''
PRINT	'@strSQL2=' + @strSQL2
PRINT ''
--
SELECT	@strSQL =
';with Localdata1 as
(
SELECT	  BusDriverID
	, BusDriverName
	, routeID
FROM OPENQUERY (TransServer1, ''' + REPLACE(@strSQL1, '''', '''''') + '''
) a
LEFT JOIN OPENQUERY(TransServer2,''' + REPLACE(@strSQL2, '''', '''''') + ''') b
on a.BusDriverID = b.BusDriverID
-- Finaloutput
SELECT	*
FROM	Localdata1'
--
PRINT '@strSQL=' + @strSQL

Hi Kristen,

I am still getting an error on next the SELECT in both SELECT statement inside @strSQL1 and @strSQL2. Is that because the Schema issue? I forgot to add the Schema name on the FROM clause. So, here are two additional line have been updated:

FROM SchemaName1.Transportation_tbl

and

FROM SchemaName2.Roadmap_tbl

so, the query still wont compile and kept saying: Incorrect syntax near the keyword 'SELECT'.

Thanks again Kristen

Hi Kristen,

I got it working. My query has the extra ",". It is working now. Thank you so much! You are so genius! How do I understand the Openquery concept? Any links can I read? Do you know?

Thanks again Kristen

I don't think there is any magic to OPENQUERY - the trick is to generate the right SQL to send to the Linked Server. IME that means outputting the generated SQL (e.g. using PRINT) and running it to check it is correct, if not adjust the manual SQL until you get the results that you want and once that works then make those changes back in the code that generates the SQL.

That's the reason that I use this two-step approach, using REPLACE to double-up the quotes, as it otherwise becomes a nightmare of doubled-up doubled-up quotes!

JOINs between multiple OPENQUERYs to remote tables (as distinct from JOINs between OPENQUERY and local tables) can have dreadful performance, so it is sometimes better to pull the results of the main OPENQUERY into a local, #TEMP, table and then construct a query for the 2nd OPENQUERY which ONLY references records actually referenced in the first resultset - but if your performance is OK you probably don't need to worry about that. Do watch out for how long it is taking / how many rows each individual OPENQUERY is bringing back (which SQL may then be "throwing away" when making the JOIN) because you could inadvertently bring the remote server to its knees!

Thank you Kristen

Personally, I don't like using the multiple single-quotes. For me it is just easier to do this:

Any embedded single-quotes will be doubled as needed.

QUOTENAME takes SYSNAME datatype as a parameter, so is restricted to 128 characters. It also returns the output as Nvarchar which may, or may not!, be desirable - e.g. then causing an implicit conversion of the rest of the expression to Nvarchar.(I'm a bit iffy about OPEN_QUERY but I think it will take 8,000 characters if the parameter is Varchar, but only 4,000 if it is Nvarchar). Big query to get to even 4,000 chars of course ...

If you don't like the multiple single-quotes and are going to use char(39) anyway then perhaps

REPLACE(@strSQL1, char(39), char(39)+char(39))

would do instead?