How to get first and last day of previous month with timestamp in SQL Server

Question

I could not find the solution which gives first and last day of previous month with timestamp. Hope this helps others. If there is already a solution for this problem I apologize.

Here is the solution.

 SELECT DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0) as FirtDayPreviousMonthWithTimeStamp,
    DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) as LastDayPreviousMonthWithTimeStamp

This will return the following if currentdate = '2012-7-31'

result: 2012-06-01 00:00:00.000 2012-06-30 23:59:59.000

This will return the following if currentdate = '2012-1-1'

result: 2011-12-01 00:00:00.000 2011-12-31 23:59:59.000

User · Answer

From SQL2012  there is a new function introduced called EOMONTH  Using this function the first and last day of the last month can be easily found  select DATEADD DD 1 EOMONTH Getdate   -2   firstdayoflastmonth  EOMONTH Getdate    -1  lastdayoflastmonth

User · Answer

This is the simplest way I know to get the first day of the previous month   Step 1  Move data to Postgres  Step 2       select date trunc  month   getdate    -  1 MONTH   interval

User · Answer

I ve not seen this solution presented yet  this is my preference for its simpler readability    select dateadd month -1 format getutcdate    yyyy-MM-01

User · Answer

To get last month s first date   select DATEADD MONTH  DATEDIFF MONTH  0  GETDATE   -1  0  LastMonthFirstDate                                                                   To get last month s last date   select DATEADD MONTH  DATEDIFF MONTH  -1  GETDATE   -1  -1  LastMonthEndDate

User · Answer

First Day Of Current Week   select CONVERT varchar dateadd week datediff week 0 getdate    0  106    Last Day Of Current Week   select CONVERT varchar dateadd week datediff week 0 getdate    6  106    First Day Of Last week   select CONVERT varchar DATEADD week datediff week 7 getdate    0  106    Last Day Of Last Week   select CONVERT varchar dateadd week datediff week 7 getdate    6  106    First Day Of Next Week   select CONVERT varchar dateadd week datediff week 0 getdate    7  106    Last Day Of Next Week   select CONVERT varchar dateadd week datediff week 0 getdate    13  106    First Day Of Current Month   select CONVERT varchar dateadd d - day getdate  -1   getdate    106    Last Day Of Current Month   select CONVERT varchar dateadd d - day dateadd m 1 getdate      dateadd m 1 getdate     106    In this Example Works on Only date is 31  and remaining days are not   First Day Of Last Month   select CONVERT varchar dateadd d - day dateadd m -1 getdate  -2    dateadd m -1 getdate  -1   106    Last Day Of Last Month   select CONVERT varchar dateadd d - day getdate     getdate    106    First Day Of Next Month   select CONVERT varchar dateadd d - day dateadd m 1 getdate  -1    dateadd m 1 getdate     106    Last Day Of Next Month   select CONVERT varchar dateadd d - day dateadd m 2 getdate      DATEADD m 2 getdate     106    First Day Of Current Year   select CONVERT varchar dateadd year datediff year 0 getdate    0  106    Last Day Of Current Year   select CONVERT varchar dateadd ms -2 dateadd year 0 dateadd year datediff year 0 getdate    1 0    106    First Day of Last Year   select CONVERT varchar dateadd year datediff year 0 getdate   -1 0  106    Last Day Of Last Year   select CONVERT varchar dateadd ms -2 dateadd year 0 dateadd year datediff year 0 getdate    0    106    First Day Of Next Year   select CONVERT varchar dateadd YEAR DATEDIFF year 0 getdate    1 0  106    Last Day Of Next Year   select CONVERT varchar dateadd ms -2 dateadd year 0 dateadd year datediff year 0 getdate    2 0    106

User · Answer

Here s a fairly straight forward and dynamic way   For last month s last day  subtract the current numeric day from today s date   For first day of last month  use the same code  just repeat subtracting the numeric day from the prior result and add 1   declare  PriorEOM as datetime declare  PriorFOM as datetime  Set  PriorEOM   dateadd day -day getdate    getdate     Set  PriorFOM   dateadd day -day  PriorEOM    1  PriorEOM   Print  PriorEOM Print  PriorFOM

User · Answer

Solution     The date format that you requested is called ODBC format  code 120     To actually calculate the values that you requested  include the following in your SQL       Copy  paste      DECLARE      FirstDayOfLastMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY DATEADD m  -1  GETDATE   - 2      DATEADD m  -1  GETDATE   - 1           LastDayOfLastMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY GETDATE       GETDATE             and use in your code           FirstDayOfLastMonth     LastDayOfLastMonth       Be aware that it has to be pasted earlier than any statements that reference the parameters  but from that point on you can reference  FirstDayOfLastMonth and  LastDayOfLastMonth in your code   Example  Let s see some code in action   DECLARE      FirstDayOfLastMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY DATEADD m  -1  GETDATE   - 2      DATEADD m  -1  GETDATE   - 1           LastDayOfLastMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY GETDATE       GETDATE      SELECT      First day of last month  AS Title  CONVERT VARCHAR   FirstDayOfLastMonth   120  AS  ODBC  UNION   SELECT       Last day of last month  AS Title  CONVERT VARCHAR   LastDayOfLastMonth   120  AS  ODBC    Run the above code to produce the following output     Note  Bear in mind that today s date for me is 12th September  2016      More  for completeness  sake    Common date parameters  Are you left wanting more   To set up a more comprehensive range of handy date related parameters  include the following in your SQL    DECLARE      FirstDayOfCurrentWeek DATETIME   CONVERT DATE  DATEADD WEEK  DATEDIFF WEEK  0  GETDATE     0          LastDayOfCurrentWeek DATETIME   CONVERT DATE  DATEADD WEEK  DATEDIFF WEEK  0  GETDATE     6          FirstDayOfLastWeek DATETIME   CONVERT DATE  DATEADD WEEK  DATEDIFF WEEK  7  GETDATE     0          LastDayOfLastWeek DATETIME   CONVERT DATE  DATEADD WEEK  DATEDIFF WEEK  7  GETDATE     6          FirstDayOfNextWeek DATETIME   CONVERT DATE  DATEADD WEEK  DATEDIFF WEEK  0  GETDATE     7          LastDayOfNextWeek DATETIME   CONVERT DATE  DATEADD WEEK  DATEDIFF WEEK  0  GETDATE     13          FirstDayOfCurrentMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY GETDATE   - 1     GETDATE            LastDayOfCurrentMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY DATEADD m  1  GETDATE        DATEADD m  1  GETDATE             FirstDayOfLastMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY DATEADD m  -1  GETDATE   - 2      DATEADD m  -1  GETDATE   - 1           LastDayOfLastMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY GETDATE       GETDATE            FirstDayOfNextMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY DATEADD m  1  GETDATE   - 1      DATEADD m  1  GETDATE             LastDayOfNextMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY DATEADD m  2  GETDATE        DATEADD m  2  GETDATE             FirstDayOfCurrentYear DATETIME   CONVERT DATE  DATEADD YEAR  DATEDIFF YEAR  0  GETDATE     0          LastDayOfCurrentYear DATETIME   CONVERT DATE  DATEADD ms  -2  DATEADD YEAR  0  DATEADD YEAR  DATEDIFF YEAR  0  GETDATE      1  0            FirstDayOfLastYear DATETIME   CONVERT DATE  DATEADD YEAR  DATEDIFF YEAR  0  GETDATE    - 1  0          LastDayOfLastYear DATETIME   CONVERT DATE  DATEADD ms  -2  DATEADD YEAR  0  DATEADD YEAR  DATEDIFF YEAR  0  GETDATE     0            FirstDayOfNextYear DATETIME   CONVERT DATE  DATEADD YEAR  DATEDIFF YEAR  0  GETDATE      1  0          LastDayOfNextYear DATETIME   CONVERT DATE  DATEADD ms  -2  DATEADD YEAR  0  DATEADD YEAR  DATEDIFF YEAR  0  GETDATE      2  0       It would make most sense to include it earlier on  preferably at the top of your procedure or SQL query    Once declared  the parameters can be referenced anywhere in your code  as many times as you need them   Example  Let s see some code in action   DECLARE      FirstDayOfCurrentWeek DATETIME   CONVERT DATE  DATEADD WEEK  DATEDIFF WEEK  0  GETDATE     0          LastDayOfCurrentWeek DATETIME   CONVERT DATE  DATEADD WEEK  DATEDIFF WEEK  0  GETDATE     6          FirstDayOfLastWeek DATETIME   CONVERT DATE  DATEADD WEEK  DATEDIFF WEEK  7  GETDATE     0          LastDayOfLastWeek DATETIME   CONVERT DATE  DATEADD WEEK  DATEDIFF WEEK  7  GETDATE     6          FirstDayOfNextWeek DATETIME   CONVERT DATE  DATEADD WEEK  DATEDIFF WEEK  0  GETDATE     7          LastDayOfNextWeek DATETIME   CONVERT DATE  DATEADD WEEK  DATEDIFF WEEK  0  GETDATE     13          FirstDayOfCurrentMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY GETDATE   - 1     GETDATE            LastDayOfCurrentMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY DATEADD m  1  GETDATE        DATEADD m  1  GETDATE             FirstDayOfLastMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY DATEADD m  -1  GETDATE   - 2      DATEADD m  -1  GETDATE   - 1           LastDayOfLastMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY GETDATE       GETDATE            FirstDayOfNextMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY DATEADD m  1  GETDATE   - 1      DATEADD m  1  GETDATE             LastDayOfNextMonth DATETIME   CONVERT DATE  DATEADD d  -  DAY DATEADD m  2  GETDATE        DATEADD m  2  GETDATE             FirstDayOfCurrentYear DATETIME   CONVERT DATE  DATEADD YEAR  DATEDIFF YEAR  0  GETDATE     0          LastDayOfCurrentYear DATETIME   CONVERT DATE  DATEADD ms  -2  DATEADD YEAR  0  DATEADD YEAR  DATEDIFF YEAR  0  GETDATE      1  0            FirstDayOfLastYear DATETIME   CONVERT DATE  DATEADD YEAR  DATEDIFF YEAR  0  GETDATE    - 1  0          LastDayOfLastYear DATETIME   CONVERT DATE  DATEADD ms  -2  DATEADD YEAR  0  DATEADD YEAR  DATEDIFF YEAR  0  GETDATE     0            FirstDayOfNextYear DATETIME   CONVERT DATE  DATEADD YEAR  DATEDIFF YEAR  0  GETDATE      1  0          LastDayOfNextYear DATETIME   CONVERT DATE  DATEADD ms  -2  DATEADD YEAR  0  DATEADD YEAR  DATEDIFF YEAR  0  GETDATE      2  0      SELECT        a  FirstDayOfCurrentWeek   AS  Title         FirstDayOfCurrentWeek AS  DATE  Server default         CONVERT VARCHAR   FirstDayOfCurrentWeek  127  AS  ISO8601        CONVERT VARCHAR   FirstDayOfCurrentWeek  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   FirstDayOfCurrentWeek  104          CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   FirstDayOfCurrentWeek  105          CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   FirstDayOfCurrentWeek  111          CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   FirstDayOfCurrentWeek  100  AS  U S         CONVERT VARCHAR   FirstDayOfCurrentWeek  120  AS  ODBC  UNION SELECT        b  LastDayOfCurrentWeek   AS  Title         LastDayOfCurrentWeek AS  DATE  Server default         CONVERT VARCHAR   LastDayOfCurrentWeek  127  AS  ISO8601        CONVERT VARCHAR   LastDayOfCurrentWeek  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   LastDayOfCurrentWeek  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   LastDayOfCurrentWeek  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   LastDayOfCurrentWeek  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   LastDayOfCurrentWeek  100  AS  U S         CONVERT VARCHAR   LastDayOfCurrentWeek  120  AS  ODBC  UNION SELECT        c  FirstDayOfLastWeek   AS  Title         FirstDayOfLastWeek AS  DATE  Server default         CONVERT VARCHAR   FirstDayOfLastWeek  127  AS  ISO8601        CONVERT VARCHAR   FirstDayOfLastWeek  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   FirstDayOfLastWeek  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   FirstDayOfLastWeek  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   FirstDayOfLastWeek  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   FirstDayOfLastWeek  100  AS  U S         CONVERT VARCHAR   FirstDayOfLastWeek  120  AS  ODBC  UNION SELECT        d  LastDayOfLastWeek   AS  Title         LastDayOfLastWeek AS  DATE  Server default         CONVERT VARCHAR   LastDayOfLastWeek  127  AS  ISO8601        CONVERT VARCHAR   LastDayOfLastWeek  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   LastDayOfLastWeek  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   LastDayOfLastWeek  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   LastDayOfLastWeek  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   LastDayOfLastWeek  100  AS  U S         CONVERT VARCHAR   LastDayOfLastWeek  120  AS  ODBC  UNION SELECT        e  FirstDayOfNextWeek   AS  Title         FirstDayOfNextWeek AS  DATE  Server default         CONVERT VARCHAR   FirstDayOfNextWeek  127  AS  ISO8601        CONVERT VARCHAR   FirstDayOfNextWeek  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   FirstDayOfNextWeek  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   FirstDayOfNextWeek  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   FirstDayOfNextWeek  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   FirstDayOfNextWeek  100  AS  U S         CONVERT VARCHAR   FirstDayOfNextWeek  120  AS  ODBC  UNION SELECT        f  LastDayOfNextWeek   AS  Title         LastDayOfNextWeek AS  DATE  Server default         CONVERT VARCHAR   LastDayOfNextWeek  127  AS  ISO8601        CONVERT VARCHAR   LastDayOfNextWeek  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   LastDayOfNextWeek  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   LastDayOfNextWeek  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   LastDayOfNextWeek  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   LastDayOfNextWeek  100  AS  U S         CONVERT VARCHAR   LastDayOfNextWeek  120  AS  ODBC  UNION SELECT        g  FirstDayOfCurrentMonth   AS  Title         FirstDayOfCurrentMonth AS  DATE  Server default         CONVERT VARCHAR   FirstDayOfCurrentMonth  127  AS  ISO8601        CONVERT VARCHAR   FirstDayOfCurrentMonth  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   FirstDayOfCurrentMonth  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   FirstDayOfCurrentMonth  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   FirstDayOfCurrentMonth  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   FirstDayOfCurrentMonth  100  AS  U S         CONVERT VARCHAR   FirstDayOfCurrentMonth  120  AS  ODBC  UNION SELECT        h  LastDayOfCurrentMonth   AS  Title         LastDayOfCurrentMonth AS  DATE  Server default         CONVERT VARCHAR   LastDayOfCurrentMonth  127  AS  ISO8601        CONVERT VARCHAR   LastDayOfCurrentMonth  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   LastDayOfCurrentMonth  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   LastDayOfCurrentMonth  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   LastDayOfCurrentMonth  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   LastDayOfCurrentMonth  100  AS  U S         CONVERT VARCHAR   LastDayOfCurrentMonth  120  AS  ODBC  UNION SELECT        i  FirstDayOfLastMonth   AS  Title         FirstDayOfLastMonth AS  DATE  Server default         CONVERT VARCHAR   FirstDayOfLastMonth  127  AS  ISO8601        CONVERT VARCHAR   FirstDayOfLastMonth  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   FirstDayOfLastMonth  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   FirstDayOfLastMonth  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   FirstDayOfLastMonth  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   FirstDayOfLastMonth  100  AS  U S         CONVERT VARCHAR   FirstDayOfLastMonth  120  AS  ODBC  UNION SELECT        j  LastDayOfLastMonth   AS  Title         LastDayOfLastMonth AS  DATE  Server default         CONVERT VARCHAR   LastDayOfLastMonth  127  AS  ISO8601        CONVERT VARCHAR   LastDayOfLastMonth  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   LastDayOfLastMonth  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   LastDayOfLastMonth  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   LastDayOfLastMonth  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   LastDayOfLastMonth  100  AS  U S         CONVERT VARCHAR   LastDayOfLastMonth  120  AS  ODBC  UNION SELECT        k  FirstDayOfNextMonth   AS  Title         FirstDayOfNextMonth AS  DATE  Server default         CONVERT VARCHAR   FirstDayOfNextMonth  127  AS  ISO8601        CONVERT VARCHAR   FirstDayOfNextMonth  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   FirstDayOfNextMonth  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   FirstDayOfNextMonth  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   FirstDayOfNextMonth  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   FirstDayOfNextMonth  100  AS  U S         CONVERT VARCHAR   FirstDayOfNextMonth  120  AS  ODBC  UNION SELECT        l  LastDayOfNextMonth   AS  Title         LastDayOfNextMonth AS  DATE  Server default         CONVERT VARCHAR   LastDayOfNextMonth  127  AS  ISO8601        CONVERT VARCHAR   LastDayOfNextMonth  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   LastDayOfNextMonth  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   LastDayOfNextMonth  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   LastDayOfNextMonth  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   LastDayOfNextMonth  100  AS  U S         CONVERT VARCHAR   LastDayOfNextMonth  120  AS  ODBC  UNION SELECT        m  FirstDayOfCurrentYear   AS  Title         FirstDayOfCurrentYear AS  DATE  Server default         CONVERT VARCHAR   FirstDayOfCurrentYear  127  AS  ISO8601        CONVERT VARCHAR   FirstDayOfCurrentYear  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   FirstDayOfCurrentYear  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   FirstDayOfCurrentYear  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   FirstDayOfCurrentYear  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   FirstDayOfCurrentYear  100  AS  U S         CONVERT VARCHAR   FirstDayOfCurrentYear  120  AS  ODBC  UNION SELECT        n  LastDayOfCurrentYear   AS  Title         LastDayOfCurrentYear AS  DATE  Server default         CONVERT VARCHAR   LastDayOfCurrentYear  127  AS  ISO8601        CONVERT VARCHAR   LastDayOfCurrentYear  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   LastDayOfCurrentYear  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   LastDayOfCurrentYear  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   LastDayOfCurrentYear  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   LastDayOfCurrentYear  100  AS  U S         CONVERT VARCHAR   LastDayOfCurrentYear  120  AS  ODBC  UNION SELECT        o  FirstDayOfLastYear   AS  Title         FirstDayOfLastYear AS  DATE  Server default         CONVERT VARCHAR   FirstDayOfLastYear  127  AS  ISO8601        CONVERT VARCHAR   FirstDayOfLastYear  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   FirstDayOfLastYear  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   FirstDayOfLastYear  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   FirstDayOfLastYear  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   FirstDayOfLastYear  100  AS  U S         CONVERT VARCHAR   FirstDayOfLastYear  120  AS  ODBC  UNION SELECT        p  LastDayOfLastYear   AS  Title         LastDayOfLastYear AS  DATE  Server default         CONVERT VARCHAR   LastDayOfLastYear  127  AS  ISO8601        CONVERT VARCHAR   LastDayOfLastYear  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   LastDayOfLastYear  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   LastDayOfLastYear  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   LastDayOfLastYear  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   LastDayOfLastYear  100  AS  U S         CONVERT VARCHAR   LastDayOfLastYear  120  AS  ODBC  UNION SELECT        q  FirstDayOfNextYear   AS  Title         FirstDayOfNextYear AS  DATE  Server default         CONVERT VARCHAR   FirstDayOfNextYear  127  AS  ISO8601        CONVERT VARCHAR   FirstDayOfNextYear  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   FirstDayOfNextYear  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   FirstDayOfNextYear  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   FirstDayOfNextYear  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   FirstDayOfNextYear  100  AS  U S         CONVERT VARCHAR   FirstDayOfNextYear  120  AS  ODBC  UNION SELECT        r  LastDayOfNextYear   AS  Title         LastDayOfNextYear AS  DATE  Server default         CONVERT VARCHAR   LastDayOfNextYear  127  AS  ISO8601        CONVERT VARCHAR   LastDayOfNextYear  103           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  British amp French        CONVERT VARCHAR   LastDayOfNextYear  104           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  German        CONVERT VARCHAR   LastDayOfNextYear  105           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Italian        CONVERT VARCHAR   LastDayOfNextYear  111           CONVERT CHAR 5    FirstDayOfCurrentWeek  108  AS  Japan        CONVERT VARCHAR   LastDayOfNextYear  100  AS  U S         CONVERT VARCHAR   LastDayOfNextYear  120  AS  ODBC     Run the above code to produce the following output     If your country is missing  then it is because I don t know the code for it  It would be most helpful and appreciated if you could please edit this answer and add a new column for your country   Thanks in advance   Note  Bear in mind that today s date for me is 12th September  2016   References  For further reading on the ISO8601 international date standard  follow this link    https   en wikipedia org wiki ISO 8601   For further reading on the ODBC international date standard  follow this link    https   technet microsoft com en-us library ms190234 v sql 90  aspx   To view the list of date formats I worked from  follow this link    https   msdn microsoft com en-GB library ms187928 aspx   For further reading on the DATETIME data type  follow this link    https   msdn microsoft com en-GB library ms187819 aspx

User · Answer

SELECT CONVERT DATE DATEADD MM  DATEDIFF MM  0  GETDATE   -1  0   AS FirstDayOfPrevMonth SELECT CONVERT DATE DATEADD MS  -3  DATEADD MM  DATEDIFF MM  0  GETDATE      0    AS LastDayOfPrevMonth

User · Answer

I have used the following logic in SSRS reports   BUS DATE   17-09-2013  X DATEADD MONTH -1 BUS DATE    17-08-2013  Y DAY BUS DATE  17  first date   DATEADD DAY -Y 1 X  01-08-2013  last date    DATEADD DAY -Y BUS DATE  31-08-2013

User · Answer

SELECT DATEADD m DATEDIFF m 0 GETDATE   -1 0  AS PreviousMonthStart  SELECT DATEADD ms -2 DATEADD month  DATEDIFF month  0  GETDATE     0   AS PreviousMonthEnd

User · Answer

You can get first and last day of previous month  with timestamp  in SQL Server by executing  --select dateadd dd -datepart dd getdate    1 dateadd mm -1 getdate     --first day of previous month  --select dateadd dd -datepart dd getdate    getdate    -- last day of previous month

User · Answer

select DATEADD MONTH  DATEDIFF MONTH  0  GETDATE   -1  0  --First day of previous month select DATEADD MONTH  DATEDIFF MONTH  -1  GETDATE   -1  -1  --Last Day of previous month

User · Answer

Take some base date which is the 31st of some month e.g. '20011231'. Then use the
following procedure (I have given 3 identical examples below, only the @dt value differs).

declare @dt datetime;

set @dt = '20140312'

SELECT DATEADD(month, DATEDIFF(month, '20011231', @dt), '20011231');



set @dt = '20140208'

SELECT DATEADD(month, DATEDIFF(month, '20011231', @dt), '20011231');



set @dt = '20140405'

SELECT DATEADD(month, DATEDIFF(month, '20011231', @dt), '20011231');

[sql-server] How to get first and last day of previous month (with timestamp) in SQL Server

The answer is

Examples related to sql-server

Examples related to datetime

Tags