none
Start/End of month

    Question

  • Hi,

    How can I get the first day of the month and the last day of the month just by selecting the month..... i.e. if I select month 3 will I be able to select 1 for the first day and then 31 for the last with just one query?

    English is not my first language.....

    Monday, March 12, 2007 3:16 PM

Answers


  • select    first_day_of_month = dateadd(month, datediff(month, 0, getdate()), 0),
        last_day_of_month  = dateadd(month, datediff(month, 0, getdate()) + 1, -1)
    Tuesday, March 13, 2007 7:44 AM

All replies

  • You would have to know the year also to determine the last day of the month.

    DECLARE @month varchar(2), @year varchar(4)
    SET @month = '3'
    SET @year = '2007'

    SELECT FirstDay = CONVERT(datetime,@month+'/01/'+@year,101),
            LastDay = DATEADD(day,-1,DATEADD(month,1,CONVERT(datetime,@month+'/01/'+@year,101)))

    Monday, March 12, 2007 8:00 PM
  • --Replace calls to GETDATE() with a variable/literal to find start and end of month
    --for a different date.

    --First day of month (subtract the day number, then add one day to get back to the 1st)
    SELECT DATEADD(dy, 1, DATEADD(dy, -1 * DAY(GETDATE()), CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime)))

    --Last day of month (add one month, then subtract the day number)
    SELECT DATEADD(dy, -1 * DAY(DATEADD(mm, 1, GETDATE())), DATEADD(mm, 1, CAST(FLOOR(CAST(GETDATE() as float)) as datetime)))
    Tuesday, March 13, 2007 2:34 AM
  • Hi,

    You can read the article http://www.kodyaz.com/content/FirstAndLastDaysOfMonth.aspx about how to find the first day of a month and the last day of a month. You can also You can download the create scripts for the sql server user defined functions GetFirstDayOfMonth and GetLastDayOfMonth from  http://www.kodyaz.com/files/21/sql_server_2000_user_defined_functions/entry335.aspx

     

     

    Eralper

    http://www.kodyaz.com

    http://www.eralper.com

     

    Tuesday, March 13, 2007 6:41 AM

  • select    first_day_of_month = dateadd(month, datediff(month, 0, getdate()), 0),
        last_day_of_month  = dateadd(month, datediff(month, 0, getdate()) + 1, -1)
    Tuesday, March 13, 2007 7:44 AM
  • Thank you to all for the reply. I went to a meeting yesterday and got back to get all the answers I needed. Thanks guys!!!!
    Tuesday, March 13, 2007 1:11 PM