DateDiff Problem

Question

Post reply

DateDiff Problem

lefrancisco1

Say Hey Kid

Points: 704
More actions
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

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

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

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

justanewone Ten Centuries Points: 1242 More actions · Answer 2

justanewone

Ten Centuries

Points: 1242

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