Sql query issue with date

Question

I have a database as attached, in which i have a BirthDate column which is split into BirthYear, BirthMonth and BirthDayNumber.

Database Image

eg. For BirthDate:

Wednesday, May 10, 1899

BirthYear:1899
BirthMonth : 5
BirthDayNumber :10

I want to sort the database in the following format( the month and date should be in ascending order and in case if the month and date are equal then it should sort the year in descending order)

01 January 2011 
02 January 2012 
02 January 2008 
05 August 2012 
06 September 2001 
12 December 1899

Can you please help me out with the query for displaying the list in this format.

BirthMonth ASC, BirthDay ASC or OrderYear DESC this is what you want? — Oleksandr Fedorenko
– Oleksandr Fedorenko, Commented Oct 4, 2012 at 6:27
I am using sqlite as I have to then connect this to an iphone application. In BirthMonth ASC, BirthDay ASC or OrderYear DESC i am getting it sorted by year. The year should be taken into consideration only if the month and date are equal. — ZealDeveloper
– ZealDeveloper, Commented Oct 4, 2012 at 6:40

David Adlington · Accepted Answer · 2012-10-04 08:31:57Z

1

Think this works

SQLFiddle

Using this code

SELECT birthdate FROM testdate 
ORDER BY 
BirthDayNumber ASC, BirthMonth ASC, BirthYear Desc

edited Oct 4, 2012 at 8:31

answered Oct 4, 2012 at 8:18

David Adlington

6764 gold badges16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Er. Anurag Jain · Accepted Answer · 2012-10-04 05:36:44Z

0

Please try query given below

SELECT * FROM `tblename`  order by BirthYear DESC, BirthMonth ASC,BirthDayNumber ASC

thanks

answered Oct 4, 2012 at 5:36

Er. Anurag Jain

1,7931 gold badge11 silver badges19 bronze badges

1 Comment

ZealDeveloper Over a year ago

I have tried this but I have to consider the year only if the day and month are equal. I this case I am getting: 02 January 2012 05 August 2012 01 January 2011 02 January 2008 06 September 2001 12 December 1899 But I require output like: 01 January 2011 02 January 2012 02 January 2008 05 August 2012 06 September 2001 12 December 1899

Ian · Accepted Answer · 2012-10-04 06:03:37Z

0

try this

SELECT * FROM TABLE ORDER BY BirthYear DESC, BirthDayNumber ASC, BirthMonth ASC

edited Oct 4, 2012 at 6:03

answered Oct 4, 2012 at 5:22

Ian

18110 bronze badges

Comments

Anil Soman · Accepted Answer · 2012-10-04 08:20:52Z

0

Try this

SELECT CONCAT(BirthDayNumber,' ', MonthName(CONCAT('1900-',CONVERT(BirthMonth,char(2)),'-1')), ' ', Birthyear)
FROM table
ORDER BY BirthMonth ASC, BirthDay ASC, BirthYear DESC

edited Oct 4, 2012 at 8:20

answered Oct 4, 2012 at 8:13

Anil Soman

2,4677 gold badges41 silver badges68 bronze badges

Comments

Vikram Jain · Accepted Answer · 2012-10-04 08:42:07Z

0

SELECT birthdate FROM testdate 
ORDER BY 
BirthMonth  ASC,BirthDayNumber  ASC, BirthYear Desc

answered Oct 4, 2012 at 8:42

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Comments

Toto · Accepted Answer · 2012-10-04 08:58:03Z

0

Try something like that:

SELECT ROW_NUMBER() OVER (PARTITION BY DT ORDER BY YE DESC, MO ASC, DT ASC) AS NR, DT, MO, YE FROM SOMEDATA

edited Oct 4, 2012 at 8:58

Toto

91.7k63 gold badges97 silver badges135 bronze badges

answered Oct 4, 2012 at 7:44

Dukagjin Maloku

1

Collectives™ on Stack Overflow

Sql query issue with date

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related