none
sql query to return all records from current month ( day lapse)

    Question

  • Hi There,

    I currently have a view , and I am looking to return all records for current month, ( I propose using script below to achieve this..)..however to complicate things further, there will be a day lapse....so when the view is looked at on the 31 august...all records from 01 -30 August will be shown....on the 01 September I would again like to show all records for 01-31 August....on September 02 this should then only show records for 01 September....

    I hope this makes sense...Would greatly appreciate peoples input on this.

    Thanks a million....(See proposed script below.)

    Aidan

    SELECT Column1,...
    FROM TableName
    WHERE
    MONTH(DateColumn) = MONTH(GetDate())
    AND
    YEAR(DateColumn) = YEAR(GetDate())

    Monday, August 19, 2013 8:11 AM

Answers

  • Couldn't you just slightly tweak your code and use...

    SELECT Column1,...
    FROM TableName
    WHERE
    MONTH(DateColumn) = MONTH(dateadd(dd, -1, GetDate()))
    AND
    YEAR(DateColumn) = YEAR(dateadd(dd, -1, GetDate()))

    This should cater for what you're asking.

    • Marked as answer by aidan1972 Thursday, August 22, 2013 10:46 AM
    Monday, August 19, 2013 8:14 AM
  • Just to make your requirements more understandable and complete:

    When the current date is not the first day of the month, you want to select all rows where <some date column> is between the first day of the month and <current date - 1>.  If the current date is the first day of the month, you want to select all rows where <some date column> is in the previous month.  Correct?  Generally speaking, queries of this type can often benefit from a calendar of dates - and you can search the forums for past discussions about why and how.   One thing you do not want to do on a regular basis is the approach you posted.  When you apply a function to a column, the database engine can't use any useful indexes containing that column - resulting in table scans generally.

    Below is one way to do this.  Note that this approach cannot be used IN a view (but with a view certainly).  It isn't clear if you were trying to create a view that encapsulates this logic.

    set nocount on;
    declare @today date; 
    set @today = CURRENT_TIMESTAMP;
    select @today as 'NOW', dateadd(day, -(day(@today)) + 1, @today) as 'FirstOfMonth'; 
    if day(@today) = 1 
    select top 10 * from dbo.STOCK_TRANSACTIONS where TRADE_DATE >= DATEADD(month, -1, @today) and TRADE_DATE < @today
    order by TRADE_DATE;
    else
    select top 10 * from dbo.STOCK_TRANSACTIONS where TRADE_DATE >= dateadd(day, -(day(@today)) + 1, @today) and TRADE_DATE < @today 
    order by TRADE_DATE;

    • Marked as answer by aidan1972 Thursday, August 22, 2013 10:46 AM
    Monday, August 19, 2013 1:42 PM

All replies

  • Couldn't you just slightly tweak your code and use...

    SELECT Column1,...
    FROM TableName
    WHERE
    MONTH(DateColumn) = MONTH(dateadd(dd, -1, GetDate()))
    AND
    YEAR(DateColumn) = YEAR(dateadd(dd, -1, GetDate()))

    This should cater for what you're asking.

    • Marked as answer by aidan1972 Thursday, August 22, 2013 10:46 AM
    Monday, August 19, 2013 8:14 AM
  • Couldn't you just slightly tweak your code and use...

    SELECT Column1,...
    FROM TableName
    WHERE
    MONTH(DateColumn) = MONTH(dateadd(dd, -1, GetDate()))
    AND
    YEAR(DateColumn) = YEAR(dateadd(dd, -1, GetDate()))

    This should cater for what you're asking.

    Oh...  and if you want to make sure you're not picking anything up from the date on which you run the report you could add an extra line...

    SELECT Column1,...
    FROM TableName
    WHERE
    MONTH(DateColumn) = MONTH(dateadd(dd, -1, GetDate()))
    AND
    YEAR(DateColumn) = YEAR(dateadd(dd, -1, GetDate()))
    and DateColumn < convert(datetime, left(GetDate(), 11))

    Monday, August 19, 2013 8:16 AM
  • Just to make your requirements more understandable and complete:

    When the current date is not the first day of the month, you want to select all rows where <some date column> is between the first day of the month and <current date - 1>.  If the current date is the first day of the month, you want to select all rows where <some date column> is in the previous month.  Correct?  Generally speaking, queries of this type can often benefit from a calendar of dates - and you can search the forums for past discussions about why and how.   One thing you do not want to do on a regular basis is the approach you posted.  When you apply a function to a column, the database engine can't use any useful indexes containing that column - resulting in table scans generally.

    Below is one way to do this.  Note that this approach cannot be used IN a view (but with a view certainly).  It isn't clear if you were trying to create a view that encapsulates this logic.

    set nocount on;
    declare @today date; 
    set @today = CURRENT_TIMESTAMP;
    select @today as 'NOW', dateadd(day, -(day(@today)) + 1, @today) as 'FirstOfMonth'; 
    if day(@today) = 1 
    select top 10 * from dbo.STOCK_TRANSACTIONS where TRADE_DATE >= DATEADD(month, -1, @today) and TRADE_DATE < @today
    order by TRADE_DATE;
    else
    select top 10 * from dbo.STOCK_TRANSACTIONS where TRADE_DATE >= dateadd(day, -(day(@today)) + 1, @today) and TRADE_DATE < @today 
    order by TRADE_DATE;

    • Marked as answer by aidan1972 Thursday, August 22, 2013 10:46 AM
    Monday, August 19, 2013 1:42 PM
  • Hi Kevin and Scott,

    Sorry about the late reply.

    Thanks a million for the information.

    That has worked a treat.

    Cheers

    Aidan

    Thursday, August 22, 2013 10:47 AM
  • nice 
    Wednesday, February 26, 2014 5:20 AM