Results 1 to 4 of 4

Thread: Need help on date subtraction

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Need help on date subtraction

    Hi.
    I have an issue.
    I need to extend some dates but here is my problem.
    I need to extend the date of a registration to the amount of November 2 and forth.
    Meaning if someone had a registration at 2020-07-15 , he has already used 3 and a half months so we should get an extension of 8 and a half.
    so the current date + 8 and a half months.

    Does this look correct?

    Code:
    DATEADD(day,DATEDIFF(day,'20201102',dateadd(year, -1, mm.membership_expires)) + DATEDIFF(day, dateadd(year, -1, mm.membership_expires),GETDATE() ),mm.membership_expires ) as addedmonths,
    Please note I'm substituting a year so I can get the initial registration date as the registrations are yearly.

    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Need help on date subtraction

    So... let me get this right... you're looking at their current membership (2021), subtracting a year (2020), figuring out how many months from then until Nov 2, 2020 (8.5 using your numbers) ... and then adding that to their current membership so it expires on Nov 2, 2022 .... have I got that correct?

    That's going to result in everone with the same expiration date of Nov2, 2022 ... so why not just set it to that?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Need help on date subtraction

    Meanwhile, what to do with those that expire currently after Nov 2? ... shorten them?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Need help on date subtraction

    After Nov 2 nothing. Fortunately no data there.
    Let me try again. 2020-07-15 , 8.5 months so the Nov 2 to July 1 is give or take 8 months? So I remove the 3,5 months from the account and then add 8 months (now that I think of it I could just add straight 8 without the calculations) , so that account have 2020-07-15 + (year 12 - removed 3,5) = 8,5) so 2020-07-15 + 8,5 (8 months 15 days give or take) = 2021-04-01 . It think I did that correct (?) .
    I'm too tired to think what the accounts that created and fall in the nov and july will do but as I've said I have none, actually 1 have one that was created 2021-06-15 so it gets the remaining till July 1 add =+ 15 days that I will change manually.

    I think I'm missing something.... I can't think right now.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width