Results 1 to 4 of 4

Thread: Days in a month

  1. #1
    Join Date
    Jul 2013
    Posts
    4

    Question Unanswered: Days in a month

    Hello!

    I have a month and a year "mm/yyyy". I want to calculate the number of days in that specific month of that year. So for example: 01/2013 would be 31. The number of days are important because I have a calculations I need to do off of that. I have seen the DateDiff function to find the number of days in the current month, but I do not know how to translate this into what I need.

    Field: DateDiff("d",Date()-(Day(Date()- 1)), DateSerial(Year _
    (Date()),(Month(Date())+1),1))

    The Table I am pulling from is called 'HOURSdata' with a field called [RDate]. Would anyone know how DateDiff could be used to my advantage, or should I use another function?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Pseudo code

    First of month: DateSerial(YourYear, YourMonth, 1)
    First of next month: DateAdd(mm, 1, FirstOfMonth)
    [ OR: DateSerial(YourYear, YourMonth + 1, 1) ]
    Number of days in month: DateDiff(dd, FirstOfMonth, FirstOfNextMonth)
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2013
    Posts
    4
    You sir, have hit the nail on the head. Thank you for your help! That worked perfect!

    SELECT DateSerial(DatePart("yyyy",[RDate]),DatePart("m",[Rdate]),1) AS [First of month], DateSerial(DatePart("yyyy",[RDate]),DatePart("m",[Rdate])+1,1) AS [First of next month], DateDiff("d",[First of month],[First of next month]) AS [Number of days in a month]
    FROM HOURSdata;

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Perhaps simpler:

    Day(DateSerial(Year([RDate]), Month([RDate]) + 1, 0))
    Paul

Posting Permissions

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