+ Reply to Thread
Results 1 to 16 of 16

SQL: First and Last Date of Previous Month

  1. #1
    Registered User
    Join Date
    06-19-2013
    Location
    Peoria, IL
    MS-Off Ver
    Excel 2010
    Posts
    18

    SQL: First and Last Date of Previous Month

    I am trying to make database connections in Excel but I keep getting the Failure to Refresh error. I have tried several things to get the stamp between the first and last date of the the previous month:

    SELECT COUNT(*)
    FROM MTS
    WHERE TRAN_CODE like '%NA7' AND FROM_BLDG = 'C' AND (STAMP between DATEADD(MONTH, DATEDIFF(MONTH, '20010101', CURRENT_TIMESTAMP), '20001231') AND DATEADD(MONTH, DATEDIFF(MONTH, '20010101', CURRENT_TIMESTAMP), '20001201');

    DateSerial(Year(Now), Month(Now)-1, 1) FDPMonth ,
    DateSerial(Year(Now), Month(Now)-1, 1+30) FDCMonth

    dateadd(m, datediff(m, #01/01/1900#, SYSDATE()-1)-1, #01/01/1900#) AND dateadd(m, datediff(m,#01/01/1900#, SYSDATE()-1), #01/01/1900#)-1

    what am I doing wrong?

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,621

    Re: SQL: First and Last Date of Previous Month

    Are you trying to get date from the last month?

    Perhaps something like this, though it's kind of unclear where you are running this query.

    MONTH(STAMP) = MONTH(CURRENT_TIMESTAMP)-1
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    06-19-2013
    Location
    Peoria, IL
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: SQL: First and Last Date of Previous Month

    I am trying to get the first date of previous month ex: 6/1/2013 and last day of previous month: 6/30/2013
    I need the SQL for this since I am using it as a data connection string.
    My access which my excel is connecting to has stamps of all dates and I want to only extract the dates between first and last dates of previous months

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,621

    Re: SQL: First and Last Date of Previous Month

    You don't use SQL in a data connection string.

    MONTH(STAMP) = MONTH(CURRENTDATE)-1 will return the same records as STAMP Between first and last sate of last month.

    For example, MONTH(6/25/2013) = 6 = MONTH(7/29/2013)-1.

  5. #5
    Registered User
    Join Date
    06-19-2013
    Location
    Peoria, IL
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: SQL: First and Last Date of Previous Month

    For workbook connections- for data connection properties: command type: table/default/sql and then command text I put in my SQL. What should I be using instead?

  6. #6
    Registered User
    Join Date
    07-15-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: SQL: First and Last Date of Previous Month

    Try this:Pls make sure your field names and table names are right in my code

    SELECT COUNT(*)
    FROM MTS WHERE TRAN_CODE like '%NA7' AND FROM_BLDG = 'C' AND format(STAMP,"mmyyyy")=format(DateAdd("m",-1,Now),"mmyyyy")
    Arun M

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,621

    Re: SQL: First and Last Date of Previous Month

    Yes, the command text is SQL, the actual data connection string is not.

    How are you setting up this data connection?

    This SQL worked fine for me and returned a count of records with TRAN_CODE ending in 'NA7', FROM_BLDG equal to 'C' and STAMP in the last month.

    SELECT COUNT(*)
    FROM MTS
    WHERE TRAN_CODE like '%NA7' AND FROM_BLDG = 'C' AND MONTH(DATE())-1=MONTH(STAMP) AND YEAR(STAMP) = YEAR(DATE())

  8. #8
    Registered User
    Join Date
    06-19-2013
    Location
    Peoria, IL
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: SQL: First and Last Date of Previous Month

    This wouldn't work when you are in January 1 of next year. The previous month would be 0 and year would not grab stamps from last year. But the rest of the code worked!

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,621

    Re: SQL: First and Last Date of Previous Month

    Good point.

    Perhaps something more like this then.

    WHERE STAMP>=DATEADD("m",-1,DATE())-DAY(DATE())+1 AND STAMP<= DATE()-DAY(DATE())

    DATEADD("m",-1,DATE())-DAY(DATE())+1 - first day of previous month

    DATE()-DAY(DATE()) - last day of previous month

  10. #10
    Registered User
    Join Date
    06-19-2013
    Location
    Peoria, IL
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: SQL: First and Last Date of Previous Month

    It's still not working with the whole code. Any other ideas?

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,621

    Re: SQL: First and Last Date of Previous Month

    What 'whole' code?

  12. #12
    Registered User
    Join Date
    06-19-2013
    Location
    Peoria, IL
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: SQL: First and Last Date of Previous Month

    SELECT (*)
    FROM MTS
    WHERE TRAN_CODE like '%NA7' AND FROM_BLDG = 'C'
    AND STAMP>=DATEADD("m",-1,DATE())-DAY(DATE())+1 AND STAMP< DATE()-DAY(DATE());

  13. #13
    Registered User
    Join Date
    07-29-2013
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    2

    Cool Re: SQL: First and Last Date of Previous Month

    This worked - it had to do with the date formats within the datediff and dateadd functions.

    select *
    from mts
    where tran_code like '%na7' and
    from_bldg = 'c' and
    datevalue(stamp)<dateadd("m",datediff("m",'1900-01-01', FORMAT(Now()-1,'YYYY-MM-DD')),'1900-01-01') and
    datevalue(stamp)>=dateadd("m",datediff("m",'1900-01-01', FORMAT(Now()-1,'YYYY-MM-DD'))-1,'1900-01-01')

    this will return all records from the previous month.

  14. #14
    Registered User
    Join Date
    07-29-2013
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: SQL: First and Last Date of Previous Month

    varying the number you add/subtract after the format statement in the datediff function will change which month you get.

    +/- 0 = first day of current month
    -1 = first day of last month
    -2 = first day of month before previous month
    +1 = first day of next month
    +2 = first day of month after next

    etc and so on and so on......

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,621

    Re: SQL: First and Last Date of Previous Month

    manali

    How exactly does it not work?

    Wrong results? Error/warning messages?

    Is STAMP a date/time field or text?

  16. #16
    Registered User
    Join Date
    07-15-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: SQL: First and Last Date of Previous Month

    Manali, did you happen to try the solution on post #6? any issues?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 10-04-2012, 07:06 AM
  2. [SOLVED] VBA to identify the current month and previous month based on system date
    By ravikumar00008 in forum Excel General
    Replies: 10
    Last Post: 07-26-2012, 10:04 AM
  3. [SOLVED] Date of the First day of the previous month
    By Hari in forum Excel General
    Replies: 5
    Last Post: 11-21-2011, 05:47 PM
  4. Display end of month date for previous month
    By erikwvb in forum Excel General
    Replies: 3
    Last Post: 06-20-2011, 01:22 PM
  5. date of last friday of previous month
    By tkaplan in forum Excel General
    Replies: 7
    Last Post: 11-14-2005, 02:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1