Solved

t-SQL format time and combine columns

Posted on 2011-09-19
5
239 Views
Last Modified: 2012-05-12
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
Comment
Question by:cdemott33
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:lludden
ID: 36563133
SELECT CONVERT(varchar(5),TimeStart,108) + ' - ' + CONVERT(varchar(5),TimeEnd,108)
0
 

Author Comment

by:cdemott33
ID: 36563211
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
 
LVL 18

Accepted Solution

by:
lludden earned 500 total points
ID: 36563343
SELECT SUBSTRING(CONVERT(varchar(20),StartTime,0),13,7) + ' - ' +  SUBSTRING(CONVERT(varchar(20),EndTime,0),13,7)
0
 
LVL 25

Expert Comment

by:jogos
ID: 36563355
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
 

Author Closing Comment

by:cdemott33
ID: 36563380
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 83
BULK INSERT most recent CSV 19 45
AWE-based memory on 32-bit servers 1 23
user defined date datatype in SQL Server- can it be overdone.. 6 25
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now