0

I need to calculate a future date by subtracting a date from another date. The catch is the date field I am subtracting has many days of the month...i.e. 10/27/2020 or 11/30/2020. What I need to do is, no matter what the day of the month is, I need to subtract the month as if it were the first day of the month no matter what it may be.

So for example I have a date 12/31/2020 I need to subtract another date 10/23/2020 from that date but I need to default 10/23/2020 to 10/01/2020 and same for any other date I would subtract from the original date. Any ideas?

XCCH004
  • 321
  • 1
  • 11
  • 3
    Please take a few moments and fill in the holes in your question by showing us sample input data along with the expected output. – Tim Biegeleisen Jun 24 '20 at 03:57
  • First day of month: https://stackoverflow.com/questions/1520789/how-can-i-select-the-first-day-of-a-month-in-sql . What do you mean by subtract i.e. do you want to find the number of days between two dates? – Alex Jun 24 '20 at 04:01

2 Answers2

1

Not really sure what you are really asking. But I'm assuming that you want to get the date gap or difference between 2 dates. But, before that, you want to set a default day for the first date.

You can set up the first date to have day 1, then use DATEDIFF to know the gap / difference.

-- source date
declare @date date = '10/27/2020'
declare @dateSubtract date = '10/23/2020'

-- break the date, take month and year only
declare @month int = DATEPART(MONTH, @date)
declare @year int = DATEPART(YEAR, @date)

-- reconstruct the date with default day=1
select @date = CAST((STR(@month) + '/01/' + STR(@year)) as date)

-- get the calculation
select DATEDIFF(DAY, @date, @dateSubtract)

The result will be (in Days),

22

You can change the DATEDIFF parameter to MONTH or YEAR.

#UPDATE 1: As mentioned by Alex in comment below, you can reconstruct the date using DATEFROMPARTS which more safer. Because casting from string may causing a confusion of date format.

Fari Fairis
  • 23
  • 6
  • It is safer to construct dates via built in functions, rather than string concatenation. See [`datefromparts`](https://learn.microsoft.com/en-us/sql/t-sql/functions/datefromparts-transact-sql?view=sql-server-ver15) function. – Alex Jun 24 '20 at 05:37
  • @Alex thanks! never know datefromparts before.. I always use CAST which sometimes causing confusion on the date format.. – Fari Fairis Jun 30 '20 at 04:59
1

You can get first day of the month and then use datediff to get the date difference.

DECLARE @table table(fromdate date,todate date)

insert into @table
VALUES ('2020-10-27','2020-12-31')

SELECT datediff(day, DATEADD(month, DATEDIFF(month, 0, fromdate), 0),todate) AS DifferenceDays
from @table

+----------------+
| DifferenceDays |
+----------------+
|             91 |
+----------------+
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58