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 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 247154 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: 247154 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.

open query rolling dates

Cookies on SQLServerCentral