Post reply

DateDiff Problem

  • August 25, 2009 at 12:07 am

    #225410

    Hi Folks,

    I Have employee details table in that Date of Birth Column exists.I have to write a stored procedure in which i should calculate the days remainining from the current date.I have used datediff between date of birth and the current date.But it is considering the doate of birth year and giving the result as more days.In this case i need the exact days remaining from current date to date of birth.

    ex: data-- Dob- 29/08/1984

    i should get the upcoming birthday as 4 days left...help me out with this task

  • August 25, 2009 at 12:39 am

    #1044001

    Hi,

    Change the year to current year because Birth date comes every year,

    then do the date different

    like

    create table #emp

    (

    ID1 int,

    name1 varchar(10),

    DOB varchar(12))

    insert into #emp

    select 101,'JOHN','27/08/1975'

    union all

    select 102,'JOH','28/09/1975'

    union all

    select 103,'JO','29/10/1975'

    select *,(datediff

    (day,

    getdate(),

    (convert(datetime,replace(DOB,right(DOB,4),(year(getdate()))),103))))AS no_of_days

    from #emp

  • August 25, 2009 at 2:45 am

    #1044026

    you could then use the SIGN() on the number of days, within a case statement. ie.

    CASE SIGN([Statement])

    WHEN -1 THEN 'Not till next year'

    WHEN 0 THEN 'Happy Birthday'

    ELSE CAST([Statement] AS VARCHAR) + SPACE(1) + 'Day(s) to go'

    END

    Dave

  • August 25, 2009 at 4:34 am

    #1044059

    hey arun,

    Thanks Dude.....its working....could you send me any code snippets for complex sql queries/articles/tutorials/websites.

    Regards,

    Leo Franklin.M

  • August 25, 2009 at 4:40 am

    #1044061

    Folks,

    I have one mre challenging task that is when we give a particular month it should tell us a second sunday.

    example data - Current month august - second sunday is 2

    likewise

    ...can u please help me out with this

  • August 25, 2009 at 4:48 am

    #1044063

    Use a calendar table

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html


    Clear Sky SQL
    My Blog[/url]

  • August 25, 2009 at 4:52 am

    #1044066

    Hi Leo,

    For the new queries, please put in to the new topic,

    Because lot of voluntaries notice the topic and give their best idea.

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

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