Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 DateDiff Problem Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, August 25, 2009 12:07 AM
 Grasshopper Group: General Forum Members Last Login: Thursday, November 15, 2012 1:15 PM Points: 14, Visits: 66
 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/1984i should get the upcoming birthday as 4 days left...help me out with this task
Post #776457
 Posted Tuesday, August 25, 2009 12:39 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, April 19, 2012 10:25 PM Points: 1,231, Visits: 3,483
 Hi,Change the year to current year because Birth date comes every year,then do the date differentlikecreate table #emp(ID1 int,name1 varchar(10),DOB varchar(12))insert into #empselect 101,'JOHN','27/08/1975'union allselect 102,'JOH','28/09/1975'union allselect 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`
Post #776465
 Posted Tuesday, August 25, 2009 2:45 AM
 Say Hey Kid Group: General Forum Members Last Login: Thursday, January 24, 2013 8:54 PM Points: 710, Visits: 1,518
 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
Post #776499
 Posted Tuesday, August 25, 2009 4:34 AM
 Grasshopper Group: General Forum Members Last Login: Thursday, November 15, 2012 1:15 PM Points: 14, Visits: 66
 hey arun, Thanks Dude.....its working....could you send me any code snippets for complex sql queries/articles/tutorials/websites.Regards,Leo Franklin.M
Post #776543
 Posted Tuesday, August 25, 2009 4:39 AM
 Grasshopper Group: General Forum Members Last Login: Thursday, November 15, 2012 1:15 PM Points: 14, Visits: 66
 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
Post #776545
 Posted Tuesday, August 25, 2009 4:48 AM
 SSCommitted Group: General Forum Members Last Login: Tuesday, May 28, 2013 3:05 AM Points: 1,943, Visits: 8,229
Post #776547
 Posted Tuesday, August 25, 2009 4:52 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, April 19, 2012 10:25 PM Points: 1,231, Visits: 3,483
 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.
Post #776550

 Permissions