open query rolling dates

Question

Post reply

open query rolling dates

spin

Hall of Fame

Points: 3274
More actions
August 16, 2011 at 7:17 am

#255779

hi
i'm querying against a linked db (unix) and am trying to create a between predicate for current date and the same date last year.
here's what i have with a fixed date:
SELECT * FROM openquery([WMSLIVE],'SELECT dsp_desp.desp_date, itm_traded_item.traded_item_code, dsp_desp_line.desp_line_qty
FROM (((PROTEUS.PUB.dsp_desp dsp_desp INNER JOIN PROTEUS.PUB.dsp_desp_line dsp_desp_line ON dsp_desp.desp_obj=dsp_desp_line.desp_obj) INNER JOIN PROTEUS.PUB.pck_pickgrp_line_detail pck_pickgrp_line_detail ON dsp_desp_line.pickgrp_line_detail_obj=pck_pickgrp_line_detail.pickgrp_line_detail_obj) INNER JOIN PROTEUS.PUB.pck_pickgrp_line pck_pickgrp_line ON (pck_pickgrp_line_detail.pickgrp_line_obj=pck_pickgrp_line.pickgrp_line_obj) AND (pck_pickgrp_line_detail.pickgrp_obj=pck_pickgrp_line.pickgrp_obj)) INNER JOIN PROTEUS.PUB.itm_traded_item itm_traded_item ON pck_pickgrp_line.traded_item_obj=itm_traded_item.traded_item_obj
WHERE (dsp_desp.desp_date>{d ''2010-06-21''} AND dsp_desp.desp_date<={d ''2011-06-21''})
ORDER BY itm_traded_item.traded_item_code') B
what's the equivalent of the tsql statement....
datediff(year, mydate, getdate()) between 0 and 1
thanks.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Is it Informix? You might do better in a Unix db forum ...

spin Hall of Fame Points: 3274 More actions · Answer 2

can i break out of the openquery and use tsql syntax??

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Where?

In Sql Server - Yes, you can. I'm sure you have a high chance that T-SQL will work there 😀

But I'm not sure about Informix... You can try and tell us :hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

spin Hall of Fame Points: 3274 More actions · Answer 4

my laughing gland has burst and i can see a long stint in intensive care ahead. 😛

i mean something like..

select * from openquery('select * from thisTable where date >= ' myTsqlDate ' and date < ' myTsqlDate -1 '')

sensible answers only please 🙂

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

spin (8/16/2011)
my laughing gland has burst and i can see a long stint in intensive care ahead. 😛
i mean something like..
select * from openquery('select * from thisTable where date >= ' myTsqlDate ' and date < ' myTsqlDate -1 '')
sensible answers only please 🙂

It seems that you are looking for a magic widget which will translate T-SQL into another db language without you having to think about it.

OpenQuery doesn't do that - it requires the query to be written in the language of the target. If there is something else out there which does, maybe someone else will chip in.