CODEWITHSUNDEEP
sqlsql-servert-sql
r.baton
r.baton

Reputation: 65

Concatenating and timing 2 date/time fields together

I'm trying to teach myself some SQL and can't figure out the best way to do the below

SELECT [BasicStartDateTime]
  ,[BasicFinishTime]
,cast (BasicStartDateTime as varchar(120)) + '-' + CAST(BasicFinishTime as varchar(7))as TimeBand 
FROM [DOCDPT].[main].[Tbl_WorkOrder]
where BasicStartDateTime >'2017-01-01'

These are my resulting columns

BasicStartDateTime

2017-01-03 12:00:00.000

BasicFinishTime 

20:00:00.000

TimeBand

Jan  3 2017 12:00PM-8:00PM

Ideally I'd want the final column (Time band) to say 12:00-20:00.

What I was trying to get to first is 12:00pm-8:00Pm and then I am going to worry about 24 hour clock bit afterwards

I've been trying to use convert and right in the first phase on the concatenation but the syntax never seems to work.

Upvotes: 0

Views: 40

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81930

If 2012+

One should note that Format() has some great functionality, but it is not known as a performer.

Example

Declare @YourTable Table ([BasicStartDateTime] datetime,[BasicFinishTime] datetime)
Insert Into @YourTable Values 
 ('2017-01-03 12:00:00.000','2017-01-03 20:00:00.000')

Select * 
      ,TimeBand = format(BasicStartDateTime,'HH:mm-')+format(BasicFinishTime,'HH:mm')
 From @YourTable

Returns

BasicStartDateTime        BasicFinishTime           TimeBand
2017-01-03 12:00:00.000   2017-01-03 20:00:00.000   12:00-20:00

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

using convert() styles, and transforming your basicfinishtime to a datetime prior to converting to varchar:

select 
    basicstartdatetime
  , basicfinishtime
  , timeband = convert(varchar(20), basicstartdatetime, 100)
    +'-'+ltrim(right(convert(varchar(20), convert(datetime,basicfinishtime),100),7))
from tbl_workorder

rextester demo: http://rextester.com/YDMCD58620

returns:

+---------------------+-----------------+----------------------------+
| basicstartdatetime  | basicfinishtime |          timeband          |
+---------------------+-----------------+----------------------------+
| 2017-01-03 12:00:00 | 20:00:00        | Jan  3 2017 12:00PM-8:00PM |
+---------------------+-----------------+----------------------------+

Upvotes: 0

sagi
sagi

Reputation: 40481

Try this:

LTRIM(RIGHT(CONVERT(VARCHAR(20), BasicStartDateTime, 100), 7)) + '-' +
LTRIM(RIGHT(CONVERT(VARCHAR(20), cast(BasicFinishTime AS datetime), 100), 7))

Upvotes: 0

Related Questions