how to make sql query to split database string into 3 different parts and compare them?

Question

what I am trying to do is: sort people in database after date of birth (nvarchar, not date type) the date is in this string format: dd-MM-yyyy and by Sort, i mean return a list of all the people, but sorted after the date of birth, to Fill a datagridview.

I have a column with the date of birth, which is nvarchar. I want to store the day, the month and the year in 3 variables in the sql query, and also test them in order to put them in order.

what I tried is:

SELECT SUBSTRING(date_of_birth,0,2) FROM people AS zi;
SUBSTRING(date_of_birth,3,2) AS luna
SUBSTRING(date_of_birth,6,4) AS an
SELECT [id], [specie], [sex], [date_of_birth], [greutate] FROM [people] WHERE

but I really don't know how to figure it out from now on...

Why not just parse it as a date if it's really a date? see: STR_TO_DATE() here: stackoverflow.com/questions/3296725/parse-date-in-mysql — user645280
– user645280, Commented Aug 28, 2013 at 20:50

MatBailie · Accepted Answer · 2013-08-28 20:48:51Z

4

SELECT
  [id], [specie], [sex], [date_of_birth], [greutate],
  SUBSTRING(date_of_birth,6,4)  AS [an],
  SUBSTRING(date_of_birth,3,2)  AS [luna],
  SUBSTRING(date_of_birth,0,2)  AS [zi]
FROM
  [people]
ORDER BY
  SUBSTRING(date_of_birth,6,4),
  SUBSTRING(date_of_birth,3,2),
  SUBSTRING(date_of_birth,0,2)

Many RDBMS also allow this...

SELECT
  [id], [specie], [sex], [date_of_birth], [greutate],
  SUBSTRING(date_of_birth,6,4)  AS [an],
  SUBSTRING(date_of_birth,3,2)  AS [luna],
  SUBSTRING(date_of_birth,0,2)  AS [zi]
FROM
  [people]
ORDER BY
  [an], [luna], [zi]

answered Aug 28, 2013 at 20:48

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

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

13 Comments

user645280 Over a year ago

"Many RDBMS also allow this..." (Yep, even MySQL)

cabral_007 Over a year ago

would something change if I want to order the people by their age? what should I calculate or... ?

MatBailie Over a year ago

@HoreaMihuţ - Ordering by DoB is mathematically the same as ordering by age... Someone who is older has a DoB longer ago...

cabral_007 Over a year ago

I've tried it, and only checked if the year is sorted ok, but it's not. I've put exactly your code in the query.

MatBailie Over a year ago

@HoreaMihuţ - Have you checked that your substrings are correct? (I copied them from your code, but I suspect that they're slightly out.)

|

Abdul Saleem · Accepted Answer · 2013-08-28 21:55:46Z

0

Try this..

.

SELECT
[id], [specie], [sex], [date_of_birth], [greutate]
From
[People]
ORDER BY
Convert(DateTime, date_of_birth, 105) Asc

.

By converting the column into DateTime type 105, you can get it to a format of 'dd-MM-yyyy'

Paste this code as it is and try..

answered Aug 28, 2013 at 21:55

Abdul Saleem

10.7k6 gold badges47 silver badges47 bronze badges

Collectives™ on Stack Overflow

how to make sql query to split database string into 3 different parts and compare them?

2 Answers 2

13 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related