August 16, 2011 at 7:17 am
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.
August 16, 2011 at 7:30 am
Is it Informix? You might do better in a Unix db forum ...
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 16, 2011 at 7:41 am
can i break out of the openquery and use tsql syntax??
August 16, 2011 at 8:18 am
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:
August 16, 2011 at 8:45 am
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 🙂
August 16, 2011 at 8:51 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply