none
How to get the date using GETDATE in an OPENQUERY? RRS feed

  • Question

  • Hi,
     
       It's really frustrating because i can't solve this issue. I have a query which is:
    SELECT * FROM OPENQUERY(SOURCE,'SELECT GETDATE() FROM lib."TABLE"')
    but it says it its an error. Please help me guys please.

    Best Regards,
    Nethan
    Thursday, January 22, 2009 7:29 AM

Answers

  • Hello Nethan

    You can try this  in DB2 to get the yesterday's date

    Select date(days(current date) - 1) from table

    Thanks

    Sreekar

    • Marked as answer by nethanjavier Monday, January 26, 2009 5:40 AM
    Friday, January 23, 2009 5:19 AM

All replies

  •  
    EXEC master..sp_addlinkedserver 'Server2005','','SQLNCLI','MYFEND\LIANGCK' 
    GO  
    SELECT * FROM OPENQUERY(Server2005,'SELECT GETDATE() FROM Northwind.dbo.Orders')  
    GO  
    EXEC master..sp_dropserver 'Server2005' 

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    Thursday, January 22, 2009 7:43 AM
  • what error you are getting?
    Your SOURCE linked server is with another Sql Server instance or with some other data source?

    If not inside OPENQUERY, you can always put getdate() in outside select.

    SELECT getdate() as Date, * FROM OPENQUERY(SOURCE,'SELECT * FROM lib."TABLE"'



    Mangal Pardeshi
    SQL With Mangal
    Thursday, January 22, 2009 7:55 AM
    Moderator
  •  The following tested OK:

    SELECT *  
    FROM OPENQUERY ( DELLSTAR,  
    'SELECT getdate() from AdventureWorks.HumanResources.Department');  
    GO  
     
    SELECT *  
    FROM OPENQUERY ( DELLSTAR, 'SELECT getdate()');  
    GO  
     

    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com     Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016



       
    Thursday, January 22, 2009 7:58 AM
    Moderator
  • Hello,

       Thank you for your help but it seems that all of those query didn't get thru. My Linked Server is DB2 and when i use those query:

    OLE DB provider "DB2OLEDB" for linked server "DB2" returned message "Token <END-OF-STATEMENT>?+ - AS <IDENTIFIER> was not valid. Valid tokens: *N.".
    Msg 7321, Level 16, State 2, Line 1
    An error occurred while preparing the query "SELECT GETDATE()" for execution against OLE DB provider "DB2OLEDB" for linked server "DB2".

    Please help me.

     

    Best Regards,

    Nethan



    Friday, January 23, 2009 1:44 AM
  • For DB2 use

    SELECT CURRENT_TIMESTAMP 

    instead of getdate().

    Let us know if works.


    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI:  www.SQLUSA.com  Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016



    Friday, January 23, 2009 2:01 AM
    Moderator
  • Hi SQLUSA,

       Thank you very much, that works for me but the problem is that i want to get the date a day before. For example Today I fetch the data from DB2 and the date is 2009-01-23 10:37:01.0000 but it should be a day before like that should be 2009-01-22 10:37:01.0000 because the data  for the whole day was yesterday. Is there any other means to solve this? My idea is to subtract 1 day from the DAY format but hot is this possible? Please help me.

     

    Best Regards,

    Nethan

    Friday, January 23, 2009 2:58 AM
  • Hello Nethan

    You can try this  in DB2 to get the yesterday's date

    Select date(days(current date) - 1) from table

    Thanks

    Sreekar

    • Marked as answer by nethanjavier Monday, January 26, 2009 5:40 AM
    Friday, January 23, 2009 5:19 AM
  • Thank you very much SREEKAR...that really helped me..thanks again.

    Best Regards,

    Nethan

    Monday, January 26, 2009 5:40 AM