Some Important MYSQL commands.

Hi,




 To concat emp_sname with emp_name in the query 
ex:  select emp_id, concat(emp_sname,'(',emp_name,')') as name from emp;

 The below query used to get date with weakname.
SimpleDateFormat hdf = new SimpleDateFormat("dd/MM EEE");
ex: 28/02 fri

 To get only time from date and time field
 select tra_candidate,(select date_format(tra_at,'%H:%i:%s')) from   training_att;
Ex: Date in the format of  2014-04-01 7:27

To get first In and last out from  login report in emp wise.
select tra.tra_candidate,(select date_format(tra.tra_at,'%H:%i:%s') from training_att tra  
 where(t.tr_candidate=tra.tra_candidate) order by tra.tra_at limit 1 )as firstIn, max(date_format(tra.tra_at,'%H:%i:%s'))as lastOut
 from training_att tra,trainings t where t.tr_candidate=tra.tra_candidate and tra.tra_type='O' group by tra.tra_candidate;

To count the number of dates in the database:

ex:

 dates may be:

2014-01-01 10:00:03;
2014-01-01  03:40:43:
2014-01-02  08:06:55;

2014-01-03  22:09:48;

 result: total number of dates are:3

 query:

select count(distinct date_format(tra.tra_at,'%Y:%M:%D')) from training_att tra;