Trying to find the date of a day for each month

Question

Post reply

Trying to find the date of a day for each month

itmasterw 60042

SSCrazy

Points: 2858
More actions
April 24, 2020 at 11:38 am

#3745945

Hi,
I have been looking allover and trying different things for this but cannot see hot to do this.
In an VB.Net application the users will select 1st, 2nd, 3rd, or 4th. from one dropdown box and the day from another.
this is a payment application, where the users enter when an customer is going to pay every month.
So for example, they will be sending in to SQl Server a 4th day of every month; so the payment would be on the 4th of every month.
I need a way to calculate, the date of every month, for whatever combination that they send in here.
Thank you

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

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

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

"I need a way to calculate, the date of every month, for whatever combination that they send in here."

Not sure what this means. Define 'every'. Something like this?

DECLARE @Day TINYINT = 4;

SELECT dt = DATEFROMPARTS(YEAR(GETDATE()),months.n,@Day)
FROM
(
    VALUES
        (1)
       ,(2)
       ,(3)
       ,(4)
       ,(5)
       ,(6)
       ,(7)
       ,(8)
       ,(9)
       ,(10)
       ,(11)
       ,(12)
) months (n) ORDER BY dt

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 2

Well I meant every month.

But this is great, I never would have thought of this. I know because I have been working for a while and haven't.

Thank you

Phil Parkin SSC Guru Points: 246988 More actions · Answer 3

Every month since when? Until when?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 4

I think that may be a poor chose of words. Sorry

Because actually I am not sure I can get this to work for me.

They will send in the number of payments, which could be even something like 20.

so starting the following month from the current date, they would make a payment every month until 20 payments are received.

Thank you

Phil Parkin SSC Guru Points: 246988 More actions · Answer 5

With thanks to Itzik Ben-Gan for the inline tally table, how about this?

DECLARE @Day TINYINT = 4;
DECLARE @NumberOfPayments INT = 20;
DECLARE @StartDate DATE = DATEADD(DAY, @Day - 1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0));

WITH lv0
AS (SELECT g = 0
    UNION ALL
    SELECT 0)
    ,lv1
AS (SELECT g = 0
    FROM lv0 a
        CROSS JOIN lv0 b) -- 4
    ,lv2
AS (SELECT g = 0
    FROM lv1 a
        CROSS JOIN lv1 b) -- 16
    ,lv3
AS (SELECT g = 0
    FROM lv2 a
        CROSS JOIN lv2 b) -- 256
    ,Tally (n)
AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM lv3)
SELECT TOP (@NumberOfPayments)
       dt = DATEADD(MONTH, Tally.n - 1, @StartDate)
FROM Tally
ORDER BY Tally.n;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 6

That worked thanks I really appreciate your help.

Thank you

Phil Parkin SSC Guru Points: 246988 More actions · Answer 7

itmasterw 60042 wrote:

That worked thanks I really appreciate your help.
Thank you

Great, my pleasure and thanks for posting back. So many people just disappear without trace once they have a solution.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 8

If you'd like to short-sheet Phil's good solution a bit, try this...

DECLARE  @Day TINYINT            = 4
        ,@NumberOfPayments  INT  = 20
;
 SELECT  dt = CONVERT(DATE,DATEADD(mm,t.N,DATEADD(dd,@Day-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))))
   FROM dbo.fnTally(1,@NumberOfPayments) t
  ORDER BY t.N
GO

You can get the fnTally function from the similarly named link in my signature line below. Every database should have one of these "Swiss Army Knives of SQL" to do some really fantastic things without loops or other forms of RBAR.

--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)