Post reply

open query rolling dates

  • 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.

  • August 16, 2011 at 7:30 am

    #1369072

    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

    #1369080

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

  • August 16, 2011 at 8:18 am

    #1369102

    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]

  • August 16, 2011 at 8:45 am

    #1369122

    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

    #1369126

    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