none
How to get previous month(s) data

    Question

  • Newbie Using SQL 2008 - I need to get data by a couple of date ranges and would like to know if this can be done and if so get some direction or examples.
    1. Frequency monthly - on the first Monday following month end get all data for previous month

    2. Frequency quarterly - on first of month following quarter end prior quarter (calendar quarter minus 1 month)

    Any help is greatly appreciated.
    Wednesday, November 06, 2013 10:20 PM

Answers

  • You can play with the date ranges:

    SELECT  DATEADD(Q, DATEDIFF(Q, 0, getdate()) +1 , 0) as beginningOfNextQuarter,
    DATEADD(Q, DATEDIFF(Q, 0, getdate()) , 0) as beginningQuarter
    ,DATEADD(Q, DATEDIFF(Q, 0, getdate())-1 , 0) as beginningLastQuarter
    SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1,0) as StartOfLastMonth
    ,DATEADD(mm, DATEDIFF(mm,0,GETDATE()),0) as StartOfThisMonth

    Wednesday, November 06, 2013 10:51 PM
  • Hi,

    Here is the way to get the start and end date of previous month for one given date

    declare @dt1 datetime
    set @dt1=convert(datetime,'20130904',112)
    select convert(datetime,substring(convert(varchar,dateadd(mm,-1,@dt1),112),1,6)+'01',112) [Start date],dateadd(dd,-1,convert(datetime,substring(convert(varchar,@dt1,112),1,6)+'01',112)) [End date]


    Many Thanks & Best Regards, Hua Min

    Thursday, November 07, 2013 4:55 AM

All replies

  • You can play with the date ranges:

    SELECT  DATEADD(Q, DATEDIFF(Q, 0, getdate()) +1 , 0) as beginningOfNextQuarter,
    DATEADD(Q, DATEDIFF(Q, 0, getdate()) , 0) as beginningQuarter
    ,DATEADD(Q, DATEDIFF(Q, 0, getdate())-1 , 0) as beginningLastQuarter
    SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1,0) as StartOfLastMonth
    ,DATEADD(mm, DATEDIFF(mm,0,GETDATE()),0) as StartOfThisMonth

    Wednesday, November 06, 2013 10:51 PM
  • Hi,

    Here is the way to get the start and end date of previous month for one given date

    declare @dt1 datetime
    set @dt1=convert(datetime,'20130904',112)
    select convert(datetime,substring(convert(varchar,dateadd(mm,-1,@dt1),112),1,6)+'01',112) [Start date],dateadd(dd,-1,convert(datetime,substring(convert(varchar,@dt1,112),1,6)+'01',112)) [End date]


    Many Thanks & Best Regards, Hua Min

    Thursday, November 07, 2013 4:55 AM