Join fields to alias in select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Join fields to alias in select statement

Author

Topic

Razzle00
Starting Member

35 Posts

Posted - 2012-12-06 : 14:32:04

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-06 : 15:04:00

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-06 : 15:07:38

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-06 : 15:14:23

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

Razzle00
Starting Member

35 Posts

Posted - 2012-12-06 : 15:57:59

Thank you very much for the replies. That code works great!

Razzle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources