Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unhappy Unanswered: Calculating Dates

    I have a table in my sql server that calculates renewal dates for me, that date is based on the final suit date. The table has the ssn, and the FinalSuitDate its in a one to many relationship with the employeetable.

    the Finalsuit table is suppose to calculate the renewal dates(which I'm trying to do in a query) my original expression in access was using dateserial FirstRenewal: DateSerial(Year([FINALSUITDONE])+2,Month([FINALSUITDONE])+1,Day([FINALSUITDONE])=30), but sql does not recognize that.

    For Example

    If the Finalsuit is 12/01/2000
    then the renewal would be 12/31/2002
    the renewal are to be done on the last day of the month and two years from the finalsuit date. Problems is I'm having problems doing this in sql servers query?

    Can someone out there help please

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think I've got the syntax right on this, but I just had a beer so can't be (hic) sure:

    dateadd(day, -1, convert(char(10), dateadd(month, 25, [FINALSUITDONE]), 120)+'-01')

    Adds 25 months to [FINALSUITDONE] to get the month FOLLOWING the month you want. Then CONVERTS to the first day of that month, and the subtracts 1 day to get that last day of the month 24 months (2 years) from the date you started with.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Question error message

    thank you for replying, but it gave me an error message saying "your entry can not be converted to a valid datetime entry"

    So frustrated

    thank you for trying to help though I sure do appreciate it.
    back to the books

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    do this SELECT CAST(FINALSUITDONE AS DATETIME) FROM table. See if that gives you an error. If it does, you need to find out whatever entry is not a proper datetime and fix it before anything you do like this will work.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, do this:

    1) Wait 2 hours after drinking a beer before posting any forum answers.
    2) Post the correct syntax:

    dateadd(day, -1, convert(char(7), dateadd(month, 25, [FINALSUITDONE]), 120)+'-01')

    ...should have use "convert(char(7)..." instead of "convert(char(10)..."
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    hmmmmmmmm, IT'S BRIIIIIILIANT.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brilliant!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking What a Drunken Genious

    Thank you so much Blindman for your help, I was really starting to get stressed out. By the way your very funny, I'll take your advice and have a couple of beers.

    Wish I had your knowledge, still working towards it

  9. #9
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Quote Originally Posted by desireemm
    What a drunken genious
    We have a lot of them here ....
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

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