select case 
    when CONVERT(varchar(8),'2012-05-04 07:03:29',113) between '00:02:00' and '13:29:00'
    then '上午' else '下午' end as 时间段
/*
时间段
----------
下午
*/

------解决方案--------------------
SQL code
declare @date datetime
set @date='2012-05-04 07:03:29'
select case when convert(char(5),@date,108)<'13:30' and convert(char(5),@date,108)>'00:02' then '上午' else '下午' end

------解决方案--------------------

SQL code

select case 
    when right(CONVERT(varchar(20),'2012-05-04 07:03:29',120),8) between '00:02:00' and '13:29:00'
    then '上午' else '下午' end as 时间段
/*
时间段
上午
*/