Selecting a particular date the following month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting a particular date the following month

Author

Topic

thenearfuture
Starting Member

35 Posts

Posted - 2006-12-20 : 13:39:09

Hi,

I've been wrestling with this problem for a while, but my newbie SQL skills are no match for it, so I'm hoping somebody here can point me in the right direction.

I have the following table, called AccountPayments:

PaymentID CaseID_FK AmountPaid InvoiceDate PaymentDate

12 135 250 01/16/2006 01/31/2006
13 136 150 01/16/2006 01/31/2006
14 135 200 01/24/2006 02/05/2006
15 136 300 01/24/2006 02/05/2006
16 139 120 01/25/2006 02/07/2006
17 136 100 01/25/2006 02/10/2006
18 137 115 01/25/2006 02/13/2006
19 136 110 02/12/2006 02/25/2006
20 139 310 02/12/2006 03/06/2006
21 138 220 02/15/2006 03/09/2006

I would like to select all the entries where the payment date is, at the latest, the 7th day of the month following the one in which the invoice was issued.

In other words: If the invoice date is in January, I would like to select all the entries where the payment date is February 7th at the latest. If the invoice date is in February, I would like to select all the entries where the payment date is March 7th at the latest.

So, for the above table, I would like to get the following result:

PaymentID CaseID_FK AmountPaid InvoiceDate PaymentDate
12 135 250 01/16/2006 01/31/2006
13 136 150 01/16/2006 01/31/2006
14 135 200 01/24/2006 02/05/2006
15 136 300 01/24/2006 02/05/2006
16 139 120 01/25/2006 02/07/2006
19 136 110 02/12/2006 02/25/2006
20 139 310 02/12/2006 03/06/2006

Does anybody know if it's possible to do this? I'm working with SQL Server 2000 and have been playing around with dateadd, but I can't seem to figure it out.

Thank you for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 13:50:48

SELECT DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 37)

Peter Larsson
Helsingborg, Sweden

thenearfuture
Starting Member

35 Posts

Posted - 2006-12-20 : 14:22:45

It worked perfectly!

If I want to change the date to the 10th of the following month, then it's 40 instead of 37, right?

- Tusind tak for hjælpen from your neighbor to the south.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 14:32:28

Yes, just change the value of 37 to 40.
This number is just an offset of SQL Server date zero, which is January 1st 1900.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 14:53:14

Hey, another Dane!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources