MySQL MONTH Clause – Get Month Name Instead of Number

javaMySQLmysql-5.5

My query is

SELECT MONTH(`hire_date`) AS month FROM `emp_tbl`

I'm getting output in Java fx

if(resultSet.next()){
int month = resultset.getInt('month');           // output is like 11

}

Then I used swtich statement to get month in string. I want output like 'November' instead of 11. I dont want to use switch statment.
Is there is any other solution to get month name as a string instead of a number?

Any other solution besides SWITCH statement?

Best Answer

SELECT MONTHNAME('2018-11-03');

... should give 'November'. So use MONTHNAME instead of MONTH.

Another option is the DATE_FORMAT function. This should give the same result as above:

SELECT DATE_FORMAT('2018-11-03', '%M');

Use %b instead of %M to get abbreviated month names (Jan, Feb etc).

Related Solutions

Mysql – How to do aggregate functions in GROUP_CONCAT

Well there are a few issues that I see with your existing query. You current sql statement is the following:

SELECT tsf.Admno,tsf.AdmissionFee,tsf.SplFee,tsf.TutionFee,
  tsf.ComputerFee,tsf.Stationary,tsf.Transport,tsf.Hostelfee,
  tsf.Lab,tsf.Library,tsf.Miscell,ta.StdNm, 
  sum(tfc.AdmissionFee,tfc.SplFee,tfc.TutionFee,tfc.ComputerFee,tfc.Stationary,tfc.Transport,tfc.Hostelfee,tfc.Lab,tfc.Library,tfc.Miscell) as 'feepaid', 
  (tsf.AdmissionFee,tsf.SplFee,tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,tsf.Miscell - sum(tfc.AdmissionFee,tfc.SplFee,tfc.TutionFee,tfc.ComputerFee,tfc.Stationary,tfc.Transport,tfc.Hostelfee,tfc.Lab,tfc.Library,tfc.Miscell)) as 'outstanding fee' 
FROM tbl_stdfees tsf, tbl_Admission ta, tbl_feecollection tfc 
where tsf.admno = ta.admno 
  and tfc.sadmno = tsf.admno 
group by ta.stdnm;

The problem is that you are attempting to aggregate multiple columns in your sum(). The sum() can only aggregate one column at a time.

Second, in my opinion your tables tbl_stdfees and tbl_feecollection are poorly designed. You have basically created a spreadsheet in you table to contain the fees required and then the fees that were paid. The problem is if you need to add a new fee you have to add a new column, that demonstrates a poor design.

I don't think that you need to use dynamic SQL to get the result, you need to unpivot or normalize the data in those two tables. To unpivot the data you will use a UNION ALL query to convert the multiple columns into multiple rows.

The query to unpivot the data would be similar to the following:

tbl_stdfees:

select admno, 'admissionfee' col, admissionfee value
from tbl_stdfees
union all
select admno, 'splfee' col, splfee value
from tbl_stdfees
union all
select admno, 'tutionfee' col, tutionfee value
from tbl_stdfees

tbl_feecollection:

select sadmno, 'admissionfee' col, admissionfee value
from tbl_feecollection
union all
select sadmno, 'splfee' col, splfee value
from tbl_feecollection
union all
select sadmno, 'tutionfee' col, tutionfee value
from tbl_feecollection

Once you have unpivoted the data, you can easily get the total fees for each admno and the total fees paid. The entire query to get the result would be:

select tsf.admno, tsf.admissionfee, tsf.splfee, tsf.tutionfee,
  tsf.computerfee, tsf.stationary, tsf.transport, tsf.hostelfee,
  tsf.lab, tsf.library, tsf.miscell,
  fc.feepaid,
  tf.Totalfees - fc.feepaid OustandingFees
from tbl_stdfees tsf
inner join tbl_Admission ta
  on tsf.admno = ta.admno
inner join
(
  select admno, sum(value) TotalFees
  from
  (
    select admno, 'admissionfee' col, admissionfee value
    from tbl_stdfees
    union all
    select admno, 'splfee' col, splfee value
    from tbl_stdfees
    union all
    select admno, 'tutionfee' col, tutionfee value
    from tbl_stdfees
    union all
    select admno, 'computerfee' col, computerfee value
    from tbl_stdfees
    union all
    select admno, 'stationary' col, stationary value
    from tbl_stdfees
    union all
    select admno, 'transport' col, transport value
    from tbl_stdfees
    union all
    select admno, 'hostelfee' col, hostelfee value
    from tbl_stdfees
    union all
    select admno, 'lab' col, lab value
    from tbl_stdfees
    union all
    select admno, 'library' col, library value
    from tbl_stdfees
    union all
    select admno, 'miscell' col, miscell value
    from tbl_stdfees
  ) sf
  group by admno
) tf 
  on tsf.admno = tf.admno
left join
(
  select sadmno, sum(value) feepaid
  from
  (
    select sadmno, 'admissionfee' col, admissionfee value
    from tbl_feecollection
    union all
    select sadmno, 'splfee' col, splfee value
    from tbl_feecollection
    union all
    select sadmno, 'tutionfee' col, tutionfee value
    from tbl_feecollection
    union all
    select sadmno, 'computerfee' col, computerfee value
    from tbl_feecollection
    union all
    select sadmno, 'stationary' col, stationary value
    from tbl_feecollection
    union all
    select sadmno, 'transport' col, transport value
    from tbl_feecollection
    union all
    select sadmno, 'hostelfee' col, hostelfee value
    from tbl_feecollection
    union all
    select sadmno, 'lab' col, lab value
    from tbl_feecollection
    union all
    select sadmno, 'library' col, library value
    from tbl_feecollection
    union all
    select sadmno, 'miscell' col, miscell value
    from tbl_feecollection
  ) fe
  group by sadmno
) fc
  on ta.admno = fc.sadmno;

See SQL Fiddle with Demo.

As I said I would suggest that you normalize the tables in your database. I would suggest making the following changes to your database.

Create a payment table:

CREATE TABLE `tbl_payment` (
  `auto_id` int(11) NOT NULL auto_increment,
  `GrupNm` varchar(40) NOT NULL,
  `class` varchar(30) NOT NULL,
  `rollno` int(2) NOT NULL,
  `sadmno` int(11) NOT NULL,
  `PaM` varchar(30) NOT NULL,
  `DDCN` int(25) NOT NULL,
  `bank` varchar(30) NOT NULL,
  `cheqd` date NOT NULL,
  `remarks` varchar(150) NOT NULL,
  `pdate` date NOT NULL,
  PRIMARY KEY  (`auto_id`)
);

Create a table to join the standard fees to each admno:

create table tbl_stdfee_admin
(
  `id` int(11),
  `Admno` varchar(50) default NULL,
  `fee_id` int(11) NOT NULL,
  `std_fee` float(10,2) default '0.00'
);

Then create a table to join the admno to each payment and fee collected:

create table tbl_collfee_admin
(
  `id` int(11),
  `payment_id` int(11),
  `Admno` varchar(50) default NULL,
  `fee_id` int(11) NOT NULL,
  `coll_fee` float(10,2) default '0.00'
);

If you normalize the tables, then you would use a query similar to the following:

select tsf.admno, 
  max(case when f.feename = 'Admission Fee' then tsf.std_fee else 0 end) AdmissionFee,
  max(case when f.feename = 'Spl Fee' then tsf.std_fee else 0  end) SplFee,
  max(case when f.feename = 'Tution Fee' then tsf.std_fee else 0  end) TutionFee,
  max(case when f.feename = 'Computer Fee' then tsf.std_fee else 0  end) ComputerFee,
  max(case when f.feename = 'Stationary' then tsf.std_fee else 0  end) Stationary,
  max(case when f.feename = 'Transport' then tsf.std_fee else 0  end) Transport,
  max(case when f.feename = 'Hostel Fee' then tsf.std_fee else 0  end) HostelFee,
  max(case when f.feename = 'Lab' then tsf.std_fee else 0  end) Lab,
  max(case when f.feename = 'Library' then tsf.std_fee else 0  end) Library,
  max(case when f.feename = 'Miscell' then tsf.std_fee else 0  end) Miscell,
  fc.FeesPaid,
  tf.TotalFees - fc.FeesPaid OustandingFees
from tbl_feevars f
left join tbl_stdfee_admin tsf
  on f.id = tsf.fee_id
left join tbl_Admission ta
  on tsf.admno = ta.admno
left join
(
  select admno, sum(std_fee) TotalFees
  from tbl_stdfee_admin
  group by admno
) tf
  on tsf.admno = tf.admno
left join
(
  select admno, sum(coll_fee) FeesPaid
  from tbl_collfee_admin
  group by admno
) fc
  on ta.admno = fc.admno
where tsf.admno is not null
group by tsf.admno, fc.FeesPaid, tf.TotalFees;

See SQL Fiddle with Demo. The second version could then be converted into a dynamic SQL version to get a list of all of the fees that you have in the tbl_feevars tables. Both versions will give a result:

| ADMNO | ADMISSIONFEE | SPLFEE | TUTIONFEE | COMPUTERFEE | STATIONARY | TRANSPORT | HOSTELFEE | LAB | LIBRARY | MISCELL | FEEPAID | OUSTANDINGFEES |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|     1 |         6000 |      0 |         0 |        2500 |       1500 |         0 |         0 |   0 |       0 |    3000 |    1400 |          11600 |
|     2 |         5000 |      0 |         0 |        2500 |       1500 |         0 |         0 |   0 |       0 |    5000 |    3000 |          11000 |

Mysql – Load data infile query for Particular Values

IMHO there is no a perfect solution for such scenario but you can utilize IGNORE clause.

For example if you were to have an auto_increment id column in your table and you already have a row in it with a known id then your query to import only rows for february might look like this

LOAD DATA local INFILE  '/path/to/your/file.txt' IGNORE
INTO TABLE slsdata 
FIELDS TERMINATED BY '\t'   
LINES TERMINATED BY '\n'  
IGNORE 1 LINES
(@sldate)
SET id = IF(MID(@sldate, 6, 2) = '02', NULL, 1), sldate = @sldate

What it does it reads values of sldate field in @slfield variable then in SET clause it checks whether it's february and if it is then assigns NULL to id column and if not then assigned predefined existing value 1 violating a UNIQUE constraint and IGNORE clause does exactly that it skips a row in such a case.

That being said I'd recommend another approach:

create a temporary staging table without any constraints
load whole file in it
do all necessary cleansing, validating against ref tables, transformations etc. on required subset of data (e.g. for particular month) utilizing all SQL capabilities
insert required subset of data from staging table into your factual table
truncate or drop staging table

Best Answer

Related Solutions

Mysql – How to do aggregate functions in GROUP_CONCAT

Mysql – Load data infile query for Particular Values

Related Question