none
SQL sysdate

    Question

  • I'm trying to run a select statement in a reporting tool. And one of the select criteria is that it must return all data which was modified last month. So one of the coloumns has this date in the format YYYY/MM.

    At the minute this has to be entered manually, as there is a user interface that can be used infront of the SQL.

    I was wondering if there was a way to make this run automatically without user entry. i.e. replace:

    SELECT *
    FROM table1
    WHERE date BETWEEN '2005/06' AND '2005/07'

    to something more like

    SELECT *
    FROM table1
    WHERE date >= ((sysdate, YYYY MM)MM, -1) 
    AND date < (sysdate, YYYY MM)

    I know the above is a bad attempt but just trying to show the problem.

    Thanks in advance.

    Friday, September 23, 2005 2:31 PM

All replies

  • Do you mean you want to show only the data corresponding to the current month?
    If so, this might help:


    SELECT *
    FROM table1
    WHERE MONTH(date) = MONTH(GETDATE()) AND YEAR(date) = YEAR(GETDATE())

    Note that the date format doesn't matter if you use this code.

    I hope this helps.

    Friday, September 23, 2005 5:52 PM
  • That sounds nearly about right but bascially im trying to compare it with a variable. and only want last months data.

    i.e:

    SELECT *
    FROM table
    WHERE TO_CHAR(var1, 'YYYY/MM') >= '2005/08'
    AND TO_CHAR(var1, 'YYYY/MM') < '2005/09'

    What i'm wanting to automate is the 2005/08 and the 2005/09, so i can compare value in that format.
    Monday, September 26, 2005 8:39 AM