asked on

SQL

Hi,

I have problem with dates.

I want to calculate years of employe and string should look like this
'yymmdd'

I have field date_birth

I want to select years from date_birth till today.
and order by years (yymmdd)

Select date_birth, year(yymmdd) from emp

Thx.

Raja Jegan R

This should do:

Select date_birth,convert(varchar(10),getdate(),112) from emp
order by convert(varchar(10),getdate(),112)

Replace getdate() with your datetime column before running the query..

Raja Jegan R

If you want to have yy instead of yyyy then run the below script

Select date_birth,convert(varchar(10),getdate(),12) from emp
order by convert(varchar(10),getdate(),12)

elconomeno

i think this is not the full answer as you want, but it is a good start :

DATEDIFF(yyyy, BirthDAy, GETDATE()) AS Age In Years

hrvica5

ASKER

Yes i know that, but i need exact years, months and days.

for example
If somebody is born yestrday it should write 000001 (yymmdd)

Thx

Raja Jegan R

Got your question wrongly earlier and this should do:

declare @date_birth datetime;
set @date_birth = '2000-01-01'

Select @date_birth,
cast(datediff(yy, @date_birth, GETDATE()) as varchar(2)) +  '-' +
cast(datediff(mm, @date_birth, GETDATE()) % 12 as varchar(2)) +  '-' + 
cast((datediff(dd, @date_birth, GETDATE()) % 365 ) % 30 as varchar(2))

Open in new window

hrvica5

ASKER

rrjegan17:
This not calculate exact, i think it does not care about days

this calculate exact age but i need in format yymmdd, i need more mmdd

select date_birth, 
CASE WHEN DATEADD(YEAR, DATEDIFF (YEAR, date_birth, CURRENT_TIMESTAMP), date_birth) > CURRENT_TIMESTAMP
THEN DATEDIFF(YEAR, date_birth, CURRENT_TIMESTAMP) - 1
ELSE DATEDIFF(YEAR, date_birth, CURRENT_TIMESTAMP)
END AS 'age'  from  emp

Open in new window

ASKER CERTIFIED SOLUTION

deighton

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Raja Jegan R

>> this calculate exact age but i need in format yymmdd, i need more mmdd

My script provided above will give you the result in yy-mm-dd format..
If you want it in format yymmdd, then try this..

declare @date_birth datetime;
set @date_birth = '2000-01-01'

Select @date_birth,
cast(datediff(yy, @date_birth, GETDATE()) as varchar(2)) + 
cast(datediff(mm, @date_birth, GETDATE()) % 12 as varchar(2)) + 
cast((datediff(dd, @date_birth, GETDATE()) % 365 ) % 30 as varchar(2))

Open in new window

hrvica5

ASKER

thank you all for your help

This was my soution

Select 

RIGHT('000000' + cast(CASE WHEN DATEADD(YEAR, DATEDIFF (YEAR, date_birth, CURRENT_TIMESTAMP), date_birth) > CURRENT_TIMESTAMP
THEN (DATEDIFF(YEAR, date_birth, CURRENT_TIMESTAMP) - 1)* 10000
ELSE DATEDIFF(YEAR, date_birth, CURRENT_TIMESTAMP) * 10000
END + 
100*(MONTH(CURRENT_TIMESTAMP) - MONTH(date_birth) - CASE WHEN DAY(CURRENT_TIMESTAMP) < DAY(date_birth) THEN
CASE WHEN MONTH(CURRENT_TIMESTAMP) - MONTH(date_birth)<0 THEN -12+1
WHEN MONTH(CURRENT_TIMESTAMP) - MONTH(date_birth)=0 THEN
CASE WHEN DAY(CURRENT_TIMESTAMP) < DAY(date_birth) THEN -11 ELSE 0 END
ELSE 1 END 
ELSE 0 END)  +
datediff(day,dateadd(month,
MONTH(CURRENT_TIMESTAMP) - MONTH(date_birth) - CASE WHEN DAY(CURRENT_TIMESTAMP) < DAY(date_birth) THEN
CASE WHEN MONTH(CURRENT_TIMESTAMP) - MONTH(date_birth)<0 THEN -12+1 
WHEN MONTH(CURRENT_TIMESTAMP) - MONTH(date_birth)=0 THEN
CASE WHEN DAY(CURRENT_TIMESTAMP) < DAY(date_birth) THEN -11 ELSE 0 END
ELSE 1 END 
ELSE 0 END 
,dateadd(year
,CASE WHEN DATEADD(YEAR, DATEDIFF (YEAR, date_birth, CURRENT_TIMESTAMP), date_birth) > CURRENT_TIMESTAMP
THEN (DATEDIFF(YEAR, date_birth, CURRENT_TIMESTAMP) - 1)
ELSE DATEDIFF(YEAR, date_birth, CURRENT_TIMESTAMP)
END
,date_birth)),CURRENT_TIMESTAMP) 
  as varchar),6) as AGE

from
emp

Open in new window

Raja Jegan R

hrvica5,

Have you tried the simpler solution posted in my comment..

hrvica5

ASKER

rrjegan 17,

Yes, I tried but it gives wrong result. try with date_birth 1947-05-11
It is not exact, it fauls for few days.

Thank you very much for your effort.