# Thread: EOM and BOM from a date

1. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579

## Unanswered: EOM and BOM from a date

Kris Wenzel posted a question via Twitter, and I'm responding here because there isn't a good way to post/format code on Twitter.

Kris' question was: A table has LoanDate. Select the last day of the month of each loandate.

My response is:
Code:
```SELECT LoanDate                                         -- Test date
,  DateAdd(m, DateDiff(m, 0, LoanDate), 0) AS BOM       -- Beginning of month
,  DateAdd(m, 1 +DateDiff(m, 0, LoanDate), -1) AS EOM   -- End of Month
FROM (
SELECT DateAdd(d, z.number, '2000-01-01') AS LoanDate   -- Cook up a few years of test dates
FROM master.dbo.spt_values AS z
WHERE  'P' = z.type ) AS zz```
-PatP

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
While thinking about "majik number" code like this (and why I generally dislike it), I found another solution that I like better! It is certainly simpler/faster and if you understand what the 30 means in this code the answer is intuitively obvious.
Code:
```SELECT
DateAdd(m, DateDiff(m, 0, LoanDate),  0) AS BOM      -- Beginning of month
,  LoanDate                                             -- Test date
,  DateAdd(m, DateDiff(m, 0, LoanDate), 30) AS EOM      -- End of Month
FROM (
SELECT DateAdd(d, z.number, 36524) AS LoanDate       -- Cook up a few years of test dates
FROM master.dbo.spt_values AS z
WHERE  'P' = z.type ) AS zz```
What does the 30 represent?

-PatP

3. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,863
I got bit by a very interesting feature* of SQL Server a little while back, so I am a little skittish around the magic numbers you mention. I take it the direct approach is liked less, due to the apparent complexity of it? If so, you can tuck it all away in a table valued function, and cross apply it anywhere you need the two values together, or create two separate scalar functions to do the calculations, if you find you only use one of the two dates more often than both. Once you parse through all the date functions, you will see the bootstrap nature of it.

Code:
```select loandate,
month (loandate),
dateadd (dd, 1 - datepart (dd, loandate), loandate) as first,
from(
SELECT DateAdd(d, z.number, 36524) AS LoanDate       -- Cook up a few years of test dates
FROM master.dbo.spt_values AS z
WHERE  'P' = z.type ) AS zz```
* Thanks and credit go to a guy here who managed to sniff out that article. My Google-fu is apparently weak.

4. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842

5. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by Thrasymachus
Why are we rolling our own?
Not everyone is running SQL 2012, and very few businesses are running SQL 2012 or later across their entire SQL Server fleet. Kris' tweet targeted SQL 2008 R2.

The solution that I posted works from SQL 2000 through SQL 2014, and while I haven't tested it on earlier versions of SQL it should run back to at least SQL 4.21.

-PatP

6. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
That tidbit was missing from your OP.

Yeah, I do not twitter. Facebook and Linkedin are enough and perhaps too much social media for me, and the way things are looking I won't be doing any legacy or sustained engineering development for the foreseeable future.

Back to my bug list.

Happy New Year everyone.

7. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
```SELECT LoanDate