List Of Employee Orderby Upcoming Birthdate

Question

0.00/5 (No votes)

See more:

hello

I wants List Of Employee Orderby Upcoming Birthdate

Suppose Today is 12-08-2015 , Then Employee wants in this Order

EmpName BDate
Anita	18-08-1988 
Mitali	28-08-1991 
Mit	04-09-1990 
Neel  04-04-2000 
Meet 10-05-1988 
Neel 22-07-2000

I have Try This

SQL

SELECT     BDATE AS BIRTHDAY, DATEDIFF(YEAR, BDATE, GETDATE()) AS AGE, DATEADD(YEAR, DATEDIFF(YEAR, BDATE, GETDATE()), BDATE) AS AddAgeInBdate,
                      DATEDIFF(DAY, GETDATE(), DATEADD(YEAR, DATEDIFF(YEAR, BDATE, GETDATE()), BDATE)) AS NoOfDaysForBdate
FROM         EMP
ORDER BY AddAgeInBdate

Posted 11-Aug-15 21:00pm

rups.seth

Updated 11-Aug-15 21:16pm

Maciej Los

v3

Add a Solution

2 solutions

Solution 2

you can achieve below query

SELECT 
  BDATE AS BIRTHDAY,[Name],
  DATEDIFF(YEAR, BDATE, GETDATE()) AS AGE,
  DATEDIFF(DAY, GETDATE(), DATEADD(YEAR, DATEDIFF(YEAR, BDATE, GETDATE()), BDATE)) AS NoOfDaysForBdate
 --FLOOR(DATEDIFF(dd,BDATE,GETDATE()) / 365.25) AS AGE_NOW
FROM 
  TestDateData
WHERE 1 = (FLOOR(DATEDIFF(dd,BDATE,GETDATE()+365) / 365.25))
          -
          (FLOOR(DATEDIFF(dd,BDATE,GETDATE()) / 365.25))

Posted 11-Aug-15 21:41pm

Suket shah

Updated 11-Aug-15 21:43pm

v5

Comments

rups.seth 12-Aug-15 4:00am

I wants output Like Follow

EmpName BDate
======================
Anita 18-08-1988
Mitali 28-08-1991
Mit 04-09-1990
Neel 04-04-2000
Meet 10-05-1988
Neel 22-07-2000

Suket shah 12-Aug-15 4:27am

above code is same like that you can check. remove whatever column you don't require

Suket shah 12-Aug-15 4:27am

SELECT
BDATE AS BIRTHDAY,[Name]
FROM
TestDateData
WHERE 1 = (FLOOR(DATEDIFF(dd,BDATE,GETDATE()+365) / 365.25))
-
(FLOOR(DATEDIFF(dd,BDATE,GETDATE()) / 365.25))

rups.seth 12-Aug-15 7:25am

column not the issue
plz see the Date Only , now 8th Month Going On So Month will be in Order like
(8 ,9 10 ,11 , 12 then 1,2,3,4....12th month)

I want date 04-04-2000 After 04-09-1990 As Per following Details

Anita 18-08-1988
Mitali 28-08-1991
Mit 04-09-1990
Neel 04-04-2000
Meet 10-05-1988
Neel 22-07-2000

hope u got it.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-08-11T21:19:00

Seems you forgot about WHERE statement:

SQL

SELECT *
FROM (
SELECT BDATE AS BIRTHDAY, DATEDIFF(YEAR, BDATE, GETDATE()) AS AGE,
    DATEADD(YEAR, DATEDIFF(YEAR, BDATE, GETDATE()), BDATE) AS AddAgeInBdate,
    DATEDIFF(DAY, GETDATE(), DATEADD(YEAR, DATEDIFF(YEAR, BDATE, GETDATE()), BDATE)) AS NoOfDaysForBdate
FROM EMP
) AS A
WHERE AddAgeInBdate>=GETDATE()
ORDER BY AddAgeInBdate

As per OP's comments:

SQL

SELECT *
FROM (
	SELECT BDATE AS BIRTHDAY, DATEDIFF(YEAR, BDATE, GETDATE()) AS AGE,
		DATEADD(YEAR, DATEDIFF(YEAR, BDATE, GETDATE()), BDATE) AS AddAgeInBdate,
		DATEDIFF(DAY, GETDATE(), DATEADD(YEAR, DATEDIFF(YEAR, BDATE, GETDATE()), BDATE)) AS NoOfDaysForBdate
	FROM EMP 
) AS A
ORDER BY CASE WHEN AddAgeInBdate>=GETDATE() THEN 0 ELSE 1 END ASC, AddAgeInBdate

Result:

1988-08-18 00:00:00.000 27  2015-08-18 00:00:00.000 6
1991-08-28 00:00:00.000 24  2015-08-28 00:00:00.000 16
1990-09-04 00:00:00.000 25  2015-09-04 00:00:00.000 23
2000-04-04 00:00:00.000 15  2015-04-04 00:00:00.000 -130
1988-05-10 00:00:00.000 27  2015-05-10 00:00:00.000 -94
2000-07-22 00:00:00.000 15  2015-07-22 00:00:00.000 -21