How to get records for the first 15 days of the month

Question

How to get records for the first 15 days of the month

Smash125

Hall of Fame

Points: 3562
More actions
August 24, 2022 at 6:26 am

#4079975

How to get records for the first 15 days of the month. please suggest
WHERE OrderDate<=DATEADD(day, 15, GetDate())

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246961 More actions · Answer 1

WHERE OrderDate >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
      AND OrderDate < DATEADD(DAY, 15, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

MarkP Old Hand Points: 305 More actions · Answer 2

To get the Orders where the OrderDate is in the first 15 days of a month:

WHERE DAY(OrderDate) <=15

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

MarkP wrote:

To get the Orders where the OrderDate is in the first 15 days of a month:
WHERE DAY(OrderDate) <=15

That will get them for all months of all years.

I would read the question a different way in the first 15 days of the current month.

So Phil’s solution would be the one I would go with.

MarkP Old Hand Points: 305 More actions · Answer 4

Ant-Green wrote:

MarkP wrote:
To get the Orders where the OrderDate is in the first 15 days of a month:
WHERE DAY(OrderDate) <=15
That will get them for all months of all years.
I would read the question a different way in the first 15 days of the current month.
So Phil’s solution would be the one I would go with.

It depends what they want - the question was ambiguous.

Obviously you could filter on what months you want, e.g. for month on month reporting

Steve Collins SSCrazy Points: 2157 More actions · Answer 5

where OrderDate>=dateadd(day, 1, eomonth(getdate(), -1))
      and OrderDate<datefromparts(year(getdate()), month(getdate()), 16)

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

MarkP wrote:

To get the Orders where the OrderDate is in the first 15 days of a month:
WHERE DAY(OrderDate) <=15

MarkP wrote:

Ant-Green wrote:
MarkP wrote:
To get the Orders where the OrderDate is in the first 15 days of a month:
WHERE DAY(OrderDate) <=15
That will get them for all months of all years.
I would read the question a different way in the first 15 days of the current month.
So Phil’s solution would be the one I would go with.
It depends what they want - the question was ambiguous.
Obviously you could filter on what months you want, e.g. for month on month reporting

The way you wrote it also makes it a non-SARGable query. That being said, I do agree that the OP did not specify what "the month" means (although the use of GETDATE() in his code suggests the Current Month). Is it the 1st thru 15th of the current month, a given month, a range of months including the year, or all months including the year? If the latter, it's not likely that SARGability would actually matter.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Smash125 wrote:

How to get records for the first 15 days of the month. please suggest
WHERE OrderDate<=DATEADD(day, 15, GetDate())

Phil's code works perfectly if you can guarantee that the OrderDate column does not contain any times as a part of the dates.

I'd like to suggest that you can't guarantee that forever. I've seen too many times where people "get the religion" and start including time and then the whole bloody company has a massive panic attack because the reports based only on dates with no times suddenly wet the bed.

My suggestion is to always do like Steve's code does and to always use a "closed/open" range like he did regardless of which proper technique you use to find the first of the month. In other words "From the 1st of the month up to and not including the 16th of the month". Then it doesn't matter if the date column includes the time or not nor does it matter what the datatype either the criteria or the datacolumn is (although datatype matching has a slight performance advantage even with temporal datatypes).

I would also "homogenize" whatever date methods you're using for ease of readability to make it easy on folks that need to do modifications or do troubleshooting in the future. Here's just one example if you really are after just the current month.

 SELECT * --Your Column List here
   FROM dbo.YourTableNameHere yt
  WHERE OrderDate >= DATEADD(dd, 1,EOMONTH(GETDATE(),-1))
    AND OrderDate <  DATEADD(dd,16,EOMONTH(GETDATE(),-1))

If this is a common lookup, you can make it a bit more flexible by turning it into an iTVF (inline Table Valued Function) where GETDATE() would be a parameter for any date of the month that you wanted to return the 1st through the 15th of.

I'll also add that I'll be happy when SQL Server 2022 comes out so that we don't have to "get clever" to find the first of the month. We can just use DATETRUNC. It still bewilders me why MS hasn't done such a thing for the last 30 years nor why they came out with a dedicated EOMonth function without coming out with an FOMonth function.

And, finally, don't forget the "old ways" like what Phil used because you're not always going to have the luxury of working on newer systems. I relate it car theft... if you want to lower the chances of your car being stolen, buy a car with a clutch and a shift-lever.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)