Chis Chis - 4 months ago 15
SQL Question

Checking the Last 2 days of the month

I want to check what are the bookings in the last 2 days of each month, I already did for the last day but I gives me some wrong data, and I need to expand it to get not only the last day but also the last 2 days of each month

SELECT Debit, Amount, Posted_Date, Posted_Time, Posted_By
FROM [dbo].[Test_data]
WHERE Booking_date IN (
SELECT MAX(Booking_date)
FROM [dbo].[Test_data]
GROUP BY MONTH(Booking_date), YEAR(Booking_date)
)

Answer

This will give you all the bookings made on the last 2 days of the month of booking. It won't work if booking_date stores time.

SELECT Debit, Amount, Posted_Date, Posted_Time, Posted_By
FROM [dbo].[Test_data]
WHERE Booking_date between 
  DATEADD(MONTH, DATEDIFF(MONTH, 0, Booking_date) + 1, 0) - 2  AND
  DATEADD(MONTH, DATEDIFF(MONTH, 0, Booking_date) + 1, 0) - 1

If you are storing time on booking_date:

SELECT Debit, Amount, Posted_Date, Posted_Time, Posted_By
FROM [dbo].[Test_data]
WHERE Booking_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, Booking_date) + 1, 0) - 2  AND
      Booking_date < DATEADD(MONTH, DATEDIFF(MONTH, 0, Booking_date) + 1, 0)
Comments