ssms to oracle openquery DATEADD

Question

New to using Openquery and trying to pull data from current month and last month. typically I would just use DATEADD for getting specific dates but OpenQuery doesn't seem to like it. Any tips on how to pull from current month and previous month?

Here is what I have tried so far.

SELECT * FROM OPENQUERY(TestServer,'

        SELECT name
            ,number
            ,create_date
            ,carton
            ,trailer
            ,bol
        FROM TEST.TESTING_VIEW1 TestV
        WHERE (TestV.create_date>=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) OR TestV.create_date IS NULL)
    ')

Answer 1

You could use:

SELECT * 
FROM OPENQUERY(TestServer,'
        SELECT name
            ,number
            ,create_date
            ,carton
            ,trailer
            ,bol
        FROM TEST.TESTING_VIEW1 TestV
        WHERE (TestV.create_date>= add_months(TRUNC(current_date,''MONTH''), -1) 
           OR TestV.create_date IS NULL)
    ');

SQL Server to Oracle:

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
<=> 
add_months(TRUNC(current_date,'MONTH'), -1) 

db<>fiddle demo