SQL SERVER – Query to Find First and Last Day of Current Month – Date Function

SQL SERVER - Query to Find First and Last Day of Current Month - Date Function bigclock-800x800 Following query will run respective to today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month. Let us see how we can do this with the help of Date Function in SQL Server.

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101),
'Last Day of Previous Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),
DATEADD(mm,1,@mydate)),101),
'Last Day of Current Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),
DATEADD(mm,1,@mydate)),101),
'First Day of Next Month'
GO

Here are few additional blog posts on this subject:

I have seen scores of expert developers getting perplexed with SQL Server in finding time only from datetime datatype. Let us have a quick glance look at the solution. Let us learn about how to get Time in Hour:Minute Format from a Datetime as well as to get Date Part Only from Datetime.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String
Next Post
SQL SERVER – MS TechNet : Storage Top 10 Best Practices

Related Posts

188 Comments. Leave new

  • Lazy solution: for fast day of the month

    select ‘1/’+
    CONVERT(varchar(25),(select MONTH(getdate())))
    +’/’+
    CONVERT(varchar(25),(select YEAR(getdate()) ))

    Reply
  • Dear Mohamed, this is shah. I provide the solution of getting Days in the month. and u provide something else

    Reply
  • BUDDHI Srilanka

    Some one need to get previous year Current month start and end date

    DECLARE @mydate DATETIME
    DECLARE @FdayOfCurrentMonth DATETIME
    DECLARE @LdayOfCurrentMonth DATETIME
    DECLARE @FdayOfCurrentMonthLY DATETIME
    DECLARE @LdayOfCurrentMonthLY DATETIME
    BEGIN
    SELECT @mydate = GETDATE()
    SELECT @FdayOfCurrentMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)
    SELECT @LdayOfCurrentMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101)
    SELECT @FdayOfCurrentMonthLY = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,-12,@mydate)),101)
    SELECT @LdayOfCurrentMonthLY = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,-11,@mydate)),101)

    Reply
    • Dont convert them to VARCHAR. Use the examples shown here. Go to the bottom of the page and refer the examples

      Reply
  • Thanks a lot, helped me quite a bit. :)

    Reply
  • Hi I am in the way of automating my SQL query. I have a filed called Transaction_Date in my table.

    I my analysis i will pre period analysis, promo period analysis and post period analysis. FOr ex, if the campaign starts on 1-June-2012 and the end date is on 30-Jun-12, then i should take the same number days as promo period for pre and post anlaysis.

    I am giving the dates manually in hte SQL code. But i want to automate it by considering the dates automatically.

    drop table #response_curve
    select Status,
    week_no,
    [Customers] = count (distinct account_no),
    [Transactions] = sum(case when tx_type_code in (’01’,’90’) then 1 when tx_type_code in (’03’,’94’) then -1 else 0 end),
    [Spend] = sum(cast(total_txn_value*issue_mult as bigint)),
    [Standard_Points_Issued]= sum(cast(std_points_value*issue_mult as bigint)),
    [Bonus_Points_Issued] = sum(cast(bonus_points_value*issue_mult as bigint))
    into #response_curve
    from
    (select case when t.transaction_date >= ‘2012-07-17’ and t.transaction_date = ‘2012-07-22’ and t.transaction_date = ‘2012-07-29’ and t.transaction_date = ‘2012-08-05’ and t.transaction_date = ‘2012-08-12’ and t.transaction_date = ‘2012-08-19’ and t.transaction_date = ‘2012-08-26’ then ‘Week7’end as week_no,
    t.account_no,tt.tx_type_code ,t.total_txn_value,tt.issue_mult,t.std_points_value,t.bonus_points_value,t.transaction_date,f.Status
    from tx_txn t with
    inner join #final_campaign_members f on f.account_no = t.account_no
    inner join tx_type tt on t.tx_type_code = tt.tx_type_code
    where t.terminal_id like ‘RSA%’ and t.transaction_date >= ‘2012-07-17’ and t.transaction_date < '2012-09-01' ) a
    group by Status,week_no

    Above is the code which i am using. please help me on howto automate the above code by not giving the dates manually

    Reply
  • Hi I need a help with the following criteria in SQL query.

    I have an end date of say ex. 4/15/2012 and I want to set this date to end of the month ie, 4/30/2012.

    Similarly, I have a beginning date as 4/16/2012 and I want to set it first of next month ie 5/1/2012. Can someone help me on this plz.

    Reply
  • Hi all,
    I need a querry that can give me endate from start date and number of months

    Like I have a start date is 10/1/2012 and number of months is 10, how can i get 7/31/2013

    Reply
  • Here’s the correct version for the blogger’s queries that takes into account the times 00:00:00 and 23:59:00

    SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0), ‘Start Last Month’
    SELECT DATEADD(minute, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)), ‘End Start Last Month’

    SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), ‘Start This Month’
    SELECT DATEADD(minute, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0)), ‘End This Month’

    Reply
  • sumit saurav (@sumit02_sec)
    December 7, 2012 1:19 am

    How to get the month having maximum birth day in a particular year in table having field DOB with datetime in sql server?

    Reply
  • Bharath Ayyagari
    December 22, 2012 4:10 pm

    Saved my time :):)

    Reply
  • senthil kumar
    January 19, 2013 5:40 pm

    i need to fetch all the details fom database by filtering name and from(startdate) to (enddate) if a name is kannan i need all his details such as projectname,task details within that filtered date for timesheet eg he done various task and list all the task he made

    Reply
  • Do not use string concatenation. Use DATADD/DATEDIFF functions. Run the below query

    SELECT
    DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0) ThisQuarterFirstDay,
    DATEADD(QQ, 0, GETDATE()) ThisQuarterLastDay,
    DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) – 1, 0) LastMonthFirstDay,
    DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), -1) LastMonthLastDay,
    DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0) ThisMonthFirstDay,
    DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, -1) ThisMonthLastDay

    Reply
  • Can also do it like this:
    Will get the first of the month 2 months ago, and first of the months 4 months in the future
    SELECT DATEADD(month, -2, GETDATE() – (DAY(GETDATE()) -1)), DATEADD(month, 4, GETDATE() – (DAY(GETDATE()) -1))

    Reply
  • Hi

    Am not very will with SQL but I just looking for. script can get first date and last date for a month from

    Reply
  • Mandy Bulin
    July 30, 2013 6:59 pm

    Thank you!!!

    Reply
  • thank you pina dave…..

    you are inspire to me………..you are question and Answer …thank you

    Reply
  • how to get first Monday in next year, by using Ms sql server 2008.Below following code will return the result as a specific(using static value +7) but i want generalized(dynamically change)query

    select
    (dateadd(day,(365-DATEPART(DY,GETDATE())+7),GETDATE())) as First_Monday_Next_Year

    Please help me………..[email removed]

    Reply
  • How do I get first day of last month and first day of last month – 1 day.

    Reply
  • how to get data for current month and next 3 months

    Reply
  • Pinal, everytime I have a SQL question, I google around and your awesome answers keep coming up. Thank you many many times!

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version