Convert stored Month Name as string into correct month number order in sql in delphi

Question

I searched through this forum and others, but can't get the solutions to work in my Delphi 2010 app.

I want to order my results by the correct month order instead of the alphabetical name.

Here is my code:

select
  *
from
  provHedis
where
  provLastName = ''' + cbPLName.Text +'''
  and
  hyear = ''' + cbYear.Text + '''
order by
  hyear, hmonth

hyear and hmonth are string fields in the table provHedis and I want to show my results in a dbgrid in order from January to February. I hope this makes sense.

What I am getting now is alphabetical order by month.

Any help appreciated. I am a beginner/novice (actually a physician) who is self taught, so please no harsh or condescending remarks.

What type of server are you using to store your data? Anyway,, you can do this either in Sql - by constructing your query so as to return a MonthNumber based on the stored Month column data and order the result set by the MonthNumber column or you can do it in the client by adding a calculated month number field to your dataset and use that for client-side ordering of your grid data. Which of these is more convenient depends on your Sql Server and Delphi DataSet type. — MartynA, Dec 08 '17 at 15:32
use `order by hmonth` not `order by hyear, hmonth` The hyear may have different years — Asad Alamdar, Dec 08 '17 at 15:36
please read about parametrized queries, do not concatenate SQL queries unless you really have to, your code is open to [SQL injection](https://en.wikipedia.org/wiki/SQL_injection). — whosrdaddy, Dec 08 '17 at 15:58
I am using ADO (based on Access 2000 MBD) within my app. The month is a string. So I'm assuming I would use MonthNumber(hmonth)? @Asad - hyear is used to keep the long results in the correct year in case of data entry error. I have used paramaters before in queries, but I wrote the table before I had the final app in mind, so I could go back and redesign the table, which I am trying to avoid. — K Boykin, Dec 08 '17 at 16:22
I don't have Access at the moment, but would `order by Val(hyear), Val(hmonth)` work. The `Val` function should convert the text to number, and numbers should sort correctly — quasoft, Dec 08 '17 at 16:36
A stupid solution could be to add an extra table, where you give every month name an int value, like 'januari', 1, 'february', 2, and so on.. Then also join on that table by month name, and now you can use the numbers of this new table for your order by — GuidoG, Dec 08 '17 at 16:42
Thanks @quasoft, but order by (Val(hmonth) still results in alphabetical order — K Boykin, Dec 08 '17 at 16:52

Asad Alamdar · Answer 1 · 2017-12-08T18:06:47.923

1

Make it a SubQuery :

select
  *
from
  provHedis
where
  provLastName = ''' + cbPLName.Text +'''
  and
  hyear = ''' + cbYear.Text + '''
    order by case hmonth
                when 'January' then 1
                when 'February' then 2
                when 'March' then 3
                when 'April' then 4
                when 'May' then 5
                when 'June' then 6
                when 'July' then 7
                when 'August' then 8
                when 'September' then 9
                when 'October' then 10
                when 'November' then 11
                when 'December' then 12
                else 13
            end, 
            hyear

OR :

ORDER BY [hyear],MONTH('1' + [hmonth] +'00')

OR SEE THIS ANSWER :

convert month name in varchar to date so as to order by month

You could have a DATETIME field that would be much easier to short by (not having to do calculations there) and if you would like to display the name of the month you could use:

DATENAME ( month, DateField )

edited Dec 08 '17 at 18:06

answered Dec 08 '17 at 17:32

Asad Alamdar

158
8

I can't get the subquery to run in Delphi or Access itself. Not sure what I'm doing wrong. I case the best thing to do is change the hyear and hmonth fields from strings to integers and get the month name from the integer at this point? I would only have a few records to convert. – K Boykin Dec 08 '17 at 17:59
see the two other choice – Asad Alamdar Dec 08 '17 at 18:02
When I run this(ORDER BY [hyear],MONTH('1' + [hmonth] +'00') in MS Access I get "Data type mismatch in criteria expression" and when I run this in Delph app I get "Syntax error in string in query expression MONTH('1' + [hmonth] +'00'. I should just change field type from string to integer at this point. Giving up trying to figure it out -- this is not like me to do, but can't keep fiddling with this. – K Boykin Dec 08 '17 at 19:04
Is compatible with SQL Server 2005 and above – Asad Alamdar Dec 08 '17 at 19:05
@KBoykin I would also suggest you to turn those fields to integers. Why? Especially in case of months you now have hardcoded month names in your database which means that if someday you decide to offer your application in multiple languages you would have big problem. But after you change month field to integer type no longer will you get ability for easy ordering but also the ability to show the month name to your users based on the language that is used on their computers. – SilverWarior Dec 08 '17 at 20:20
@KBoykin Another advantage of using integer field type for storing months is that you save up on the space since a small integer (one byte) is enough for storing month data this way. But if you are storing month data as string you probably have string field with 9 character length which is nine bytes or one byte for each character. That is if your string field is only limited to ANSI characters. In case of Unicode you need at least double of that. – SilverWarior Dec 08 '17 at 20:27
@KBoykin And another advantage is that most databases offer much faster searching capabilities for data in Integer fields in comparison to string fields, so you will probably gain even better performance from your database this way. – SilverWarior Dec 08 '17 at 20:30
@SilverWarior, I have converted month to integer, now how do I use sql in delphi to show the short month name in my dbgrid instead of the integer while still ordering by the integer? Thanks – K Boykin Dec 08 '17 at 22:20
1

You can add a calculated field and onCalcFields event add this code `adoquery1['newfield']:=FormatSettings.LongMonthNames[adoquery1.FieldByName('hmonth').AsInteger];` – Asad Alamdar Dec 09 '17 at 16:51
For short month name change this `LongtMonthNames` with `ShortMonthNames` – Asad Alamdar Dec 09 '17 at 17:24

Convert stored Month Name as string into correct month number order in sql in delphi

1 Answers1