DateDiff Problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » DateDiff Problem

DateDiff Problem

Rate Topic

Display Mode

Topic Options

Author

Message

lefrancisco1

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/1984

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

Post #776457

arun.sas

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 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

Post #776465

justanewone

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

lefrancisco1

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

lefrancisco1

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