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: http://stackoverflow.com/questions/3296725/parse-date-in-mysql — , Aug 28 '13 at 20:50

score 4 · Accepted Answer · answered Aug 28 '13 at 20:48

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 '13 at 20:48

MatBailie

83,401
18
103
137

"Many RDBMS also allow this..." (Yep, even MySQL) – Aug 28 '13 at 20:51
would something change if I want to order the people by their age? what should I calculate or... ? – cabral_007 Aug 28 '13 at 20:58
@HoreaMihuţ - Ordering by DoB is mathematically the same as ordering by age... Someone who is older has a DoB longer ago... – MatBailie Aug 28 '13 at 20:59
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. – cabral_007 Aug 28 '13 at 21:01
@HoreaMihuţ - Have you checked that your substrings are correct? *(I copied them from your code, but I suspect that they're slightly out.)* – MatBailie Aug 28 '13 at 21:03
I checked, and they are correct, if the numbering starts from 0. – cabral_007 Aug 28 '13 at 21:06
i've posted them as an answer.. couldn't get much editing in here (is that what you asked for? sorry if I didn't understood right) – cabral_007 Aug 28 '13 at 21:14
@HoreaMihuţ - Including the three substring fields? :) *(Also, you should be able to edit your question, and add the example data there?)* – MatBailie Aug 28 '13 at 21:15
sorry, i don't understand what you want me to do with the three substring fields? :-s – cabral_007 Aug 28 '13 at 21:20
ok, so no ideea why does it not work? the substrings are ok... the date string is 12-12-2013 and we take starting from 0, 2 digits for the day, starting from 3, 2 digits for the month. and starting from 6, 4 digits for the year. – cabral_007 Aug 28 '13 at 21:31
@HoreaMihuţ - Are you ***certain*** that SUBSTRING() is 0 based in MySQL? You even said you checked. Try looking at this link... http://sqlfiddle.com/#!2/d41d8/19154 Your `SUBSTRING()` formula are wrong... – MatBailie Aug 28 '13 at 21:51
you are perfectly right! I'm sorry for insisting on my stupidity :) – cabral_007 Aug 28 '13 at 21:54
thank you a lot MatBailie!! I just runned the query and now works perfect, as you said. Have a great day! :) – cabral_007 Aug 28 '13 at 21:56

score 0 · Answer 2 · answered Aug 28 '13 at 21:55

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..

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

2 Answers2