|
|
Last day of the month with MSSQL
- In accounting, the month end or the last day of the month is usually done during the first few days of the next month.
- We need some flexibility with some gymnastics to extract the dates to find the last day of the month of the previous month or the next.
- There are many way of calculated the dates. It's just another point of view, Here is another way: How to extract portions of dates with MSSQL
Applies to:
- Microsoft SQL Server 7
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
USE sql911; go SELECT CONVERT(CHAR(13),sales_date,106) AS 'Sales date', sales_qty, sales_price FROM sales; go
Sales date sales_qty sales_price
------------- --------------------------------------- ---------------------------------------
22 Oct 2005 10.00 11.75
06 Oct 2006 10.00 11.75
30 Nov 2005 0.00 11.75
06 Oct 2006 10.00 11.75
06 Dec 2005 8.00 11.75
06 Feb 2006 0.00 NULL
19 Mar 2006 5.00 11.75
06 Oct 2006 10.00 11.75
06 Oct 2006 10.00 11.75
20 Apr 2006 4.00 11.75
06 May 2006 3.00 11.75
06 Oct 2004 0.00 NULL
21 Nov 2005 9.00 11.75
06 Jun 2006 0.00 11.75
22 Jan 2006 7.00 11.75
23 Feb 2006 6.00 11.75
25 Mar 2005 5.00 11.75
31 Oct 2006 10.00 11.75
29 Oct 2006 3.00 11.75
17 Jun 2007 2.00 11.75
18 Apr 2007 0.00 11.75
31 May 2007 5.00 11.75
06 Jul 2007 1.00 11.75
03 May 2007 3.00 11.75
05 May 2007 1.00 11.75
06 Oct 2006 10.00 11.75
06 Mar 2004 0.00 NULL
06 Apr 2005 4.00 11.75
06 May 2005 3.00 11.75
06 Jun 2006 2.00 11.75
06 Jul 2006 1.00 11.75
06 Mar 2006 5.00 11.75
06 Apr 2006 4.00 11.75
07 May 2007 2.00 11.75
06 Jun 2006 0.00 NULL
06 Jul 2006 1.00 11.75
06 Oct 2005 10.00 11.75
08 May 2007 1.50 11.75
06 Dec 2005 8.00 11.75
06 Jan 2006 7.00 11.75
07 May 2007 2.00 10.50
08 May 2007 1.00 10.00
06 Jul 2006 1.00 11.75
09 May 2007 1.25 10.00
06 Nov 2007 9.00 11.75
10 May 2007 5.00 10.00
11 May 2007 0.00 10.00
12 May 2007 0.00 10.00
13 May 2007 1.00 10.00
08 May 2007 1.00 10.00
07 May 2007 2.00 10.00
14 May 2007 3.00 10.00
06 Jul 2006 1.00 11.75
11 May 2007 3.50 10.00
08 May 2007 0.00 10.00
08 May 2007 2.00 10.00
08 May 2007 3.00 10.00
(57 row(s) affected)
- Line 3: the convert(char(13),sales_date,106) is to make it easier to read the dates. It drops the time and because it's in the format: dd mmm yyyy, there is no confusion such as 2/1/07: is it? February 1st or January 2nd?
The valid formats are:
| Convert style | Convert display |
|---|
| 0 or 100 | mmm dd yyyy hh:miAM (or PM) |
| 101 | mm/dd/yyyy |
| 102 | yyyy.mm.dd |
| 103 | dd/mm/yyyy |
| 104 | dd.mm.yyyy |
| 105 | dd-mm-yyyy |
| 106 | dd mmm yyyy |
| 107 | Mmm dd, yyyy |
| 108 | hh:mm:ss |
| 9 or 109 | mmm dd yyyy hh:mi:ss:mmmAM (or PM) |
| 110 | mm-dd-yyyy |
| 111 | yyyy/mm/dd |
| 112 | yyyymmdd |
| 13 or 113 | dd mmm yyyy hh:mm:ss:mmm(24h) |
| 114 | hh:mi:ss:mmm(24h) |
| 20 or 120 | yyyy-mm-dd hh:mi:ss(24h) |
| 21 or 121 | yyyy-mm-dd hh:mi:ss.mmm(24h) |
| 126 | yyyy-mm-dd Thh:mm:ss.mmm(no spaces) |
| 130 | dd mmm yyyy hh:mi:ss:mmmAM |
| 131 | dd/mm/yyyy hh:mi:ss:mmmAM |
Getting the last day of the month
SELECT CONVERT(CHAR(13),sales_date,106) AS 'Sales date', CONVERT(CHAR(13),DATEADD(mm,1,sales_date - DAY(sales_date)+1)-1,106) AS 'Cur month', CONVERT(CHAR(13),DATEADD(mm,0,sales_date - DAY(sales_date)+1)-1,106) AS 'Prev month', CONVERT(CHAR(13),DATEADD(mm,2,sales_date - DAY(sales_date)+1)-1,106) AS 'Next month', sales_qty, sales_price FROM sales; go
Sales date Cur month Prev month Next month sales_qty sales_price
------------- ------------- ------------- ------------- --------------------------------------- ---------------------------------------
22 Oct 2005 31 Oct 2005 30 Sep 2005 30 Nov 2005 10.00 11.75
06 Oct 2006 31 Oct 2006 30 Sep 2006 30 Nov 2006 10.00 11.75
30 Nov 2005 30 Nov 2005 31 Oct 2005 31 Dec 2005 0.00 11.75
06 Oct 2006 31 Oct 2006 30 Sep 2006 30 Nov 2006 10.00 11.75
06 Dec 2005 31 Dec 2005 30 Nov 2005 31 Jan 2006 8.00 11.75
28 Feb 2006 28 Feb 2006 31 Jan 2006 31 Mar 2006 0.00 NULL
19 Mar 2006 31 Mar 2006 28 Feb 2006 30 Apr 2006 5.00 11.75
06 Oct 2006 31 Oct 2006 30 Sep 2006 30 Nov 2006 10.00 11.75
06 Oct 2006 31 Oct 2006 30 Sep 2006 30 Nov 2006 10.00 11.75
20 Apr 2006 30 Apr 2006 31 Mar 2006 31 May 2006 4.00 11.75
01 Mar 2006 31 Mar 2006 28 Feb 2006 30 Apr 2006 3.00 11.75
06 Oct 2004 31 Oct 2004 30 Sep 2004 30 Nov 2004 0.00 NULL
21 Nov 2005 30 Nov 2005 31 Oct 2005 31 Dec 2005 9.00 11.75
31 Mar 2006 31 Mar 2006 28 Feb 2006 30 Apr 2006 0.00 11.75
22 Jan 2006 31 Jan 2006 31 Dec 2005 28 Feb 2006 7.00 11.75
23 Feb 2006 28 Feb 2006 31 Jan 2006 31 Mar 2006 6.00 11.75
25 Mar 2005 31 Mar 2005 28 Feb 2005 30 Apr 2005 5.00 11.75
31 Oct 2006 31 Oct 2006 30 Sep 2006 30 Nov 2006 10.00 11.75
29 Oct 2006 31 Oct 2006 30 Sep 2006 30 Nov 2006 3.00 11.75
17 Jun 2007 30 Jun 2007 31 May 2007 31 Jul 2007 2.00 11.75
18 Apr 2007 30 Apr 2007 31 Mar 2007 31 May 2007 0.00 11.75
31 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 5.00 11.75
06 Jul 2007 31 Jul 2007 30 Jun 2007 31 Aug 2007 1.00 11.75
03 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 3.00 11.75
05 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 1.00 11.75
06 Oct 2006 31 Oct 2006 30 Sep 2006 30 Nov 2006 10.00 11.75
06 Mar 2004 31 Mar 2004 29 Feb 2004 30 Apr 2004 0.00 NULL
06 Apr 2005 30 Apr 2005 31 Mar 2005 31 May 2005 4.00 11.75
06 May 2005 31 May 2005 30 Apr 2005 30 Jun 2005 3.00 11.75
06 Jun 2006 30 Jun 2006 31 May 2006 31 Jul 2006 2.00 11.75
06 Jul 2006 31 Jul 2006 30 Jun 2006 31 Aug 2006 1.00 11.75
06 Mar 2006 31 Mar 2006 28 Feb 2006 30 Apr 2006 5.00 11.75
06 Apr 2006 30 Apr 2006 31 Mar 2006 31 May 2006 4.00 11.75
07 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 2.00 11.75
06 Jun 2006 30 Jun 2006 31 May 2006 31 Jul 2006 0.00 NULL
06 Jul 2006 31 Jul 2006 30 Jun 2006 31 Aug 2006 1.00 11.75
06 Oct 2005 31 Oct 2005 30 Sep 2005 30 Nov 2005 10.00 11.75
08 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 1.50 11.75
06 Dec 2005 31 Dec 2005 30 Nov 2005 31 Jan 2006 8.00 11.75
06 Jan 2006 31 Jan 2006 31 Dec 2005 28 Feb 2006 7.00 11.75
07 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 2.00 10.50
08 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 1.00 10.00
06 Jul 2006 31 Jul 2006 30 Jun 2006 31 Aug 2006 1.00 11.75
09 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 1.25 10.00
06 Nov 2007 30 Nov 2007 31 Oct 2007 31 Dec 2007 9.00 11.75
10 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 5.00 10.00
11 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 0.00 10.00
12 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 0.00 10.00
13 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 1.00 10.00
08 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 1.00 10.00
07 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 2.00 10.00
14 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 3.00 10.00
06 Jul 2006 31 Jul 2006 30 Jun 2006 31 Aug 2006 1.00 11.75
11 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 3.50 10.00
08 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 0.00 10.00
08 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 2.00 10.00
08 May 2007 31 May 2007 30 Apr 2007 30 Jun 2007 3.00 10.00
(57 row(s) affected)
Date calculations gymnastics
SELECT CONVERT(CHAR(13),sales_date,106) AS 'Sales date', CONVERT(CHAR(13),(sales_date - DAY(sales_date)+1)-1,106) AS 'Calc date', CONVERT(CHAR(13),DATEADD(mm,1,sales_date - DAY(sales_date)+1)-1,106) AS 'Cur month', CONVERT(CHAR(13),DATEADD(mm,0,sales_date - DAY(sales_date)+1)-1,106) AS 'Prev month', CONVERT(CHAR(13),DATEADD(mm,2,sales_date - DAY(sales_date)+1)-1,106) AS 'Next month' FROM sales; go
Sales date Calc date Cur month Prev month Next month
------------- ------------- ------------- ------------- -------------
22 Oct 2005 30 Sep 2005 31 Oct 2005 30 Sep 2005 30 Nov 2005
06 Oct 2006 30 Sep 2006 31 Oct 2006 30 Sep 2006 30 Nov 2006
30 Nov 2005 31 Oct 2005 30 Nov 2005 31 Oct 2005 31 Dec 2005
06 Oct 2006 30 Sep 2006 31 Oct 2006 30 Sep 2006 30 Nov 2006
06 Dec 2005 30 Nov 2005 31 Dec 2005 30 Nov 2005 31 Jan 2006
28 Feb 2006 31 Jan 2006 28 Feb 2006 31 Jan 2006 31 Mar 2006
19 Mar 2006 28 Feb 2006 31 Mar 2006 28 Feb 2006 30 Apr 2006
06 Oct 2006 30 Sep 2006 31 Oct 2006 30 Sep 2006 30 Nov 2006
06 Oct 2006 30 Sep 2006 31 Oct 2006 30 Sep 2006 30 Nov 2006
20 Apr 2006 31 Mar 2006 30 Apr 2006 31 Mar 2006 31 May 2006
01 Mar 2006 28 Feb 2006 31 Mar 2006 28 Feb 2006 30 Apr 2006
06 Oct 2004 30 Sep 2004 31 Oct 2004 30 Sep 2004 30 Nov 2004
21 Nov 2005 31 Oct 2005 30 Nov 2005 31 Oct 2005 31 Dec 2005
31 Mar 2006 28 Feb 2006 31 Mar 2006 28 Feb 2006 30 Apr 2006
22 Jan 2006 31 Dec 2005 31 Jan 2006 31 Dec 2005 28 Feb 2006
23 Feb 2006 31 Jan 2006 28 Feb 2006 31 Jan 2006 31 Mar 2006
25 Mar 2005 28 Feb 2005 31 Mar 2005 28 Feb 2005 30 Apr 2005
31 Oct 2006 30 Sep 2006 31 Oct 2006 30 Sep 2006 30 Nov 2006
29 Oct 2006 30 Sep 2006 31 Oct 2006 30 Sep 2006 30 Nov 2006
17 Jun 2007 31 May 2007 30 Jun 2007 31 May 2007 31 Jul 2007
18 Apr 2007 31 Mar 2007 30 Apr 2007 31 Mar 2007 31 May 2007
31 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
06 Jul 2007 30 Jun 2007 31 Jul 2007 30 Jun 2007 31 Aug 2007
03 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
05 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
06 Oct 2006 30 Sep 2006 31 Oct 2006 30 Sep 2006 30 Nov 2006
06 Mar 2004 29 Feb 2004 31 Mar 2004 29 Feb 2004 30 Apr 2004
06 Apr 2005 31 Mar 2005 30 Apr 2005 31 Mar 2005 31 May 2005
06 May 2005 30 Apr 2005 31 May 2005 30 Apr 2005 30 Jun 2005
06 Jun 2006 31 May 2006 30 Jun 2006 31 May 2006 31 Jul 2006
06 Jul 2006 30 Jun 2006 31 Jul 2006 30 Jun 2006 31 Aug 2006
06 Mar 2006 28 Feb 2006 31 Mar 2006 28 Feb 2006 30 Apr 2006
06 Apr 2006 31 Mar 2006 30 Apr 2006 31 Mar 2006 31 May 2006
07 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
06 Jun 2006 31 May 2006 30 Jun 2006 31 May 2006 31 Jul 2006
06 Jul 2006 30 Jun 2006 31 Jul 2006 30 Jun 2006 31 Aug 2006
06 Oct 2005 30 Sep 2005 31 Oct 2005 30 Sep 2005 30 Nov 2005
08 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
06 Dec 2005 30 Nov 2005 31 Dec 2005 30 Nov 2005 31 Jan 2006
06 Jan 2006 31 Dec 2005 31 Jan 2006 31 Dec 2005 28 Feb 2006
07 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
08 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
06 Jul 2006 30 Jun 2006 31 Jul 2006 30 Jun 2006 31 Aug 2006
09 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
06 Nov 2007 31 Oct 2007 30 Nov 2007 31 Oct 2007 31 Dec 2007
10 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
11 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
12 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
13 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
08 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
07 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
14 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
06 Jul 2006 30 Jun 2006 31 Jul 2006 30 Jun 2006 31 Aug 2006
11 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
08 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
08 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
08 May 2007 30 Apr 2007 31 May 2007 30 Apr 2007 30 Jun 2007
(57 row(s) affected)
- Line 2: DATEADD(mm,1,sales_date - DAY(sales_date)+1)-1. The +1)-1 is to deal with switch between 30 days and 31 days months.
- DATEADD(mm,0,sales_date - DAY(sales_date): 6-Oct-2007 would return 30-Oct-2007 and not 31-Oct-2007, the last day of the month.
|
|
