• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

t-SQL format time and combine columns

Hi All -

I have two columns... timeStart and timeEnd.  They contain the following values

timeStart = 1/1/1900 8:00 AM
timeEnd = 1/1/1900 5:00 PM

I need to cut the date out of the columns and combine the two of them into a new column called timeSpan, which would return this...

8:00 AM - 5:00 PM

I tried this....

timeStart + '-' + timeEnd As timeSpan

... but it doesn't work.  Can someone show me how I can get the result I'm looking for.

Thanks!
0
cdemott33
Asked:
cdemott33
  • 2
  • 2
1 Solution
 
lluddenCommented:
SELECT CONVERT(varchar(5),TimeStart,108) + ' - ' + CONVERT(varchar(5),TimeEnd,108)
0
 
cdemott33Author Commented:
Hi lludden - Thanks for your help.  Your code returned this value...

08:00 - 17:00

Is there a way to convert it from military time to standard time and add the AM/PM?
0
 
lluddenCommented:
SELECT SUBSTRING(CONVERT(varchar(20),StartTime,0),13,7) + ' - ' +  SUBSTRING(CONVERT(varchar(20),EndTime,0),13,7)
0
 
jogosCommented:
The complete syntax  can you find on http://msdn.microsoft.com/en-us/library/ms187928.aspx and http://msdn.microsoft.com/en-us/library/ms187748.aspx and http://msdn.microsoft.com/en-us/library/ms186323.aspx
Convert your date to a varchar (CONVERT), take a substring (SUBSTRING) from the time-part and that begins after the first space(CHARINDEX).

You can also use the DATEPART function http://msdn.microsoft.com/en-us/library/ms174420.aspx 

datepart(hour,timeStart)+':'datepart(minute,timeStart) ....but then the AMPM becomes difficult.

0
 
cdemott33Author Commented:
This worked.  Thank you!  I also made a few changes to your code based on some research I was doing and came up with this...

CONVERT(varchar(15), CAST(timeStart As Time), 100) + ' - ' + CONVERT(varchar(15), CAST(timeEnd As Time), 100) as timeSpan,

Which also works.  Thanks again everone who helped me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now