how to use a specific column for a day CREATE TABLE `csptargets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `cspcode` varchar(11) NOT NULL,
   `vol`decimal(20,3) NOT NULL DEFAULT '0.000',
   `day01` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day02` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day03` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day04` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day05` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day06` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day07` decimal(20,3) NOT NULL DEFAULT '0.000',  
   `day08` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day09` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day10` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day11` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day12` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day13` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day14` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day15` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day16` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day17` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day18` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day19` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day20` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day21` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day22` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day23` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day24` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day25` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day26` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day27` decimal(20,3)  NULL ,
   `day28` decimal(20,3)  NULL ,
   `day29` decimal(20,3)  NULL ,
   `day30` decimal(20,3)  NULL ,
   `day31` decimal(20,3)  NULL ,
   `week1` varchar(11) NOT NULL,
   `week2` varchar(11) NOT NULL,
   `week3` varchar(11) NOT NULL,
   `week4` varchar(11) NOT NULL,
   `week5` varchar(11) NOT NULL,
   `week6` varchar(11) NOT NULL,
  `cnt` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UniqueTarget` (`cspcode`,`start_date`,`end_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1


In this table I want to access a particular column. 

I can get the current date using 

SELECT @day:=DATE_FORMAT(curdate(), '%d');
set @daily:=concat ('day',@day); 


Now for today's date I have day28 .

But on using select @daily from csptargets it's giving value as 'day28', not the value I have in column.

Is there any other way to use the specific column name?
You really need to fix your design. Having 31 columns, one for every day and 6 columns, one for every week is not normalized and is going to cause a lot of pain on the road.

That aside, to solve the specific issue, you need dynamic SQL because the @daily = 'day28' is a string and you want it used as a column name:

-- the sql as a string
SET @sql = concat('select ', @daily, ' from csptargets;') ;

-- prepare a statement from the sql
PREPARE stmnt FROM @sql ;

-- execute it
EXECUTE stmnt ;


or:

-- the sql as a string
SET @sql = concat('select day', 
                   date_format(curdate(), '%d'), 
                  ' from csptargets;') ;

-- prepare a statement from the sql
PREPARE stmnt FROM @sql ;

-- execute it
EXECUTE stmnt ;