Post reply

time

  • March 26, 2009 at 10:38 am

    #201599

    Hi experts,

    Can any queries return days and months betweeen a start date and an end date ??????? I will make it clear

    If we use ' datediff ' it will return only particular months or particular days between the provided dates.but i dont want it.

    for example suppose i want to know how many dates and months between 01/11/1987 and 15/12/1988.If i use " datediff " it will return only depending on the datepart we provide.I dont need it actually.i need total number of dates along with months in between the provided dates like 12,15 where 12 represents total months and 15 represents days.So it will give there are 12 months+15 days in between 01/11/1987 and 15/12/1988.

    CAN ANYBODY HELP ME IN SOLVING THIS PROBLEM.It could be thankful if u can give me a query.

    [font="Comic Sans MS"]+++BLADE+++[/font]:cool:

  • March 26, 2009 at 11:44 am

    #966902

    the BETWEEN oerator is what you want i think;

    select * from sometable where invoice_date BETWEEN '01/01/2009' AND GETDATE()

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 26, 2009 at 11:50 am

    #966911

    THANKS buddy

    [font="Comic Sans MS"]+++BLADE+++[/font]:cool:

  • March 26, 2009 at 11:52 am

    #966913

    But this not what i meant for

    [font="Comic Sans MS"]+++BLADE+++[/font]:cool:

  • March 26, 2009 at 11:58 am

    #966919

    Try this:

    declare @Date1 datetime, @Date2 datetime;

    select @Date1 = '11/1/1987', @Date2 = '12/15/1988';

    select

    (cast(convert(char(8), @Date2, 112) as int) - cast(convert(char(8), @Date1, 112) as int))/10000 as Years,

    datediff(month, @Date1, @Date2)%12 as Months,

    datediff(day, @Date1, dateadd(month, -1 * (datediff(month, @Date1, @Date2)%12), dateadd(year, -1 * (cast(convert(char(8), @Date2, 112) as int) - cast(convert(char(8), @Date1, 112) as int))/10000, @Date2))) as Days

    I haven't done extensive testing on it. It should be okay, but you'll want to test it a few times.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • March 26, 2009 at 12:05 pm

    #966925

    Thanks man !!!!!!!! :w00t:

    you got it!!!!!;-)

    this is what actually i want 🙂

    [font="Comic Sans MS"]+++BLADE+++[/font]:cool:

  • March 26, 2009 at 12:22 pm

    #966945

    Glad I could help. You're welcome.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 7 posts - 1 through 6 (of 6 total)

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