SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join fields to alias in select statement		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Razzle00
Starting Member

35 Posts
Posted - 12/06/2012 :  14:32:04  Show Profile  Reply with Quote
Hi,

Can someone show me how to build a select statement to join fields together into one alias field. I need to make a character starttime and endtime field using the fields from this table;

[start_ampm] [char](2) NOT NULL,
[start_hour] [decimal](2, 0) NOT NULL,
[start_min] [decimal](2, 0) NOT NULL,
[end_ampm] [char](2) NOT NULL,
[end_hour] [decimal](2, 0) NOT NULL,
[end_min] [decimal](2, 0) NOT NULL,

Example data:
start_hour = 9
start_min = 30
start_ampm = 'AM'

end_hour = 2
end_min = 45
en_ampm = 'PM'

result alias would look like this..

number would need to be paded 2 spaces with zero's.
starttime = '09:30 AM'
endtime = '02:45 PM'

Any help appreciated,

Razzle

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 12/06/2012 :  15:04:00  Show Profile  Reply with Quote
For start, see below. Similarly for end
RIGHT('0'+CAST(start_hour AS VARCHAR(2)),2)
 +':'
 +RIGHT('0'+CAST(start_min AS VARCHAR(2)),2)
 +' '
 + start_ampm
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 12/06/2012 :  15:07:38  Show Profile  Reply with Quote
This should do it

SELECT right('0'+convert(varchar(8),Start_hour),2)
+':' + right('0'+convert(varchar(8),Start_min),2)
+' ' + start_ampm

FROM yourTable


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 12/06/2012 :  15:14:23  Show Profile  Reply with Quote
That's 2x today Sunita that I refreshed my browser to make sure no one answerd only to find myself beaten to the punch by you!! i'm starting to fell foolish (and old and slow)

Jim

but I did take the time to add FROM yourTable



Everyday I learn something that somebody else already knew
Go to Top of Page

Razzle00
Starting Member

35 Posts
Posted - 12/06/2012 :  15:57:59  Show Profile  Reply with Quote
Thank you very much for the replies. That code works great!

Razzle
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000