SQL Server Performance

SQL Server Min Date limit

Discussion in 'General Developer Questions' started by jadoger1, Sep 12, 2005.

  1. jadoger1 New Member

    Is there a way to change SQL Server Min date limit which is I beleive year 1753?

    I am retriving data from Progress Database using Linked Server and in progress there are dates beyound 1753 which cannot be changed....

    As soon as I fire the Linked query using OpenQuery

    SELECT * FROM OPENQUERY(LINKEDSERVER,'SELECT ABC.amount,ABC.Open_Date FROM ABC')

    it fails with this error


    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].


    I beleive While retriveing Data (when you fire up SQL Query from Query Analyzer) SQL server tries to store retrived data in a temporary table and then shows values and as there are some dates beyound 1753 SQL Server fails to save that data in temporary place.

    it would also be helpfull if some one can tell me how to force SQL Server to retrive DateTime data from progress as varchar?

    I have search Progress SQL but there is no function like CAST as the progress version we are using is SQL89 complaint.

    Many thanks
  2. FrankKalis Moderator

    You can use any of the T-SQL String functions on a DATETIME, such a STR(), LTRIM() to cast a DATETIME to a string while SELECT. Not sure, if this will help you , though.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  3. FrankKalis Moderator

    ...and you better use CONVERT before any other function...<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT CONVERT(CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />,GETDATE(),112)<br /></font id="code"></pre id="code"><br /><br />Sorry for the editing. Will fetch another cup of coffee now. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  4. jadoger1 New Member

    FranKalis<br />I am talking about Linked Server Query i.e. query being straight passed to Progress Database, and progress Database doesn't understand what CONVERT is <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />SELECT * FROM OPENQUERY(LINKEDSERVER,'SELECT ABC.amount,ABC.Open_Date FROM ABC')
  5. FrankKalis Moderator

    Sh*t!! Shame on me...[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  6. Madhivanan Moderator

    Frank, I think you need more Coffee [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />jadoger1, Doesnt Progress Database support any kind of formations?<br />If possible convert it into varchar type<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  7. jadoger1 New Member

    As I said, Its old progress DB (8.3) which is SQL-89 complaint and there is as such no CAST function <img src='/community/emoticons/emotion-6.gif' alt=':(' />
  8. Madhivanan Moderator

    Do one thing
    If possible add 100 years to that date when querying then using convert function in SQL Server you can subtract 100 years and show it as varchar. I am not sure whether this will work

    Madhivanan

    Failing to plan is Planning to fail
  9. jadoger1 New Member

    gr8 tip!
    TRYING....
  10. jadoger1 New Member

    Boom Boom Boom!

    Query running and results are comming back.

    U r the star Madhivanan....

    Shld
  11. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by jadoger1</i><br /><br />Boom Boom Boom!<br /><br />Query running and results are comming back.<br /><br />U r the star Madhivanan....<br /><br />Shld<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Glad to know I have helped you [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

Share This Page