Advertisement

07.09.2008 at 12:09PM PDT, ID: 23551458
[x]
Attachment Details

extract time as hour with am/pm from datetime

Asked by charlesbaldo in SQL Server 2005, MS SQL Server

Tags: microsoft, sql server, 2005

Hi I have a date time field in sql server 2005 that I want to extract the hour with am or pm after it. I created a monstrosity to do this, it works but there must be a better way?

      cast(
            case
              when
                  cast(substring(convert(varchar(10), ttimeoffirstmetering, 108),1,2) as integer) > 12 then
                    cast(substring(convert(varchar(10), ttimeoffirstmetering, 108),1,2) as integer)-12
              when
                   substring(convert(varchar(10), ttimeoffirstmetering, 108),1,2)='00' then 12
              else
                    cast(substring(convert(varchar(10), ttimeoffirstmetering, 108),1,2) as integer)
              end
              as varchar(10))+
            case
              when
                  cast(substring(convert(varchar(10), ttimeoffirstmetering, 108),1,2) as integer) > 12 then
                     'pm'
              else
                     'am'
              end  
       as v_timeStart Free Trial
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
cast(
		case 
		  when
			cast(substring(convert(varchar(10), ttimeoffirstmetering, 108),1,2) as integer) > 12 then
			  cast(substring(convert(varchar(10), ttimeoffirstmetering, 108),1,2) as integer)-12
		  when 
			 substring(convert(varchar(10), ttimeoffirstmetering, 108),1,2)='00' then 12
		  else
			  cast(substring(convert(varchar(10), ttimeoffirstmetering, 108),1,2) as integer)
		  end 
		  as varchar(10))+
		case 
		  when
			cast(substring(convert(varchar(10), ttimeoffirstmetering, 108),1,2) as integer) > 12 then
			   'pm'
		  else
			   'am'
		  end  
	 as v_time
 
 
Loading Advertisement...
 
[+][-]07.09.2008 at 12:16PM PDT, ID: 21967245

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]07.09.2008 at 12:16PM PDT, ID: 21967250

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.09.2008 at 12:19PM PDT, ID: 21967271

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: SQL Server 2005, MS SQL Server
Tags: microsoft, sql server, 2005
Sign Up Now!
Solution Provided By: aaronakin
Participating Experts: 2
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628