t-sql help!

• Mh-397891

  SSCertifiable

  Points: 6704

  More actions

  July 9, 2009 at 12:12 pm

  #132628

  I want to convert the following time values into HH:MIAM or HH:MIPM,

  I have the following data in my table:

  Create table #temp

  (time char(4))

  Insert into #temp

  select '0900'

  union

  select '0915'

  union

  select '0930'

  union

  select '1200'

  union

  select '1300'

  union

  select '1515'

  union

  select '1530'

  union

  select '1545'

  union

  select '1600'

  select * from #temp

  I want for example the following output:

  9AM for '0900'

  3:45PM for '1545'

  Thanks!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 9, 2009 at 12:41 pm

  #1022607

  Try the attached code. Once again, I can't seem to post the code directly in this thread.

• Jeffrey Williams

  SSC Guru

  Points: 90305

  More actions

  July 9, 2009 at 2:24 pm

  #1022673

  Here is a different method (thanks Lynn for the setup):

  If object_id('tempdb..#temp', 'U') Is Not Null

  Drop Table #temp;

  Create Table #temp (time char(4));

  Insert Into #temp

  Select '0900'

  Union Select '0915'

  Union Select '0930'

  Union Select '1200'

  Union Select '1300'

  Union Select '1515'

  Union Select '1530'

  Union Select '1545'

  Union Select '1600'

  Select convert(char(7), right(dateadd(day, 0, stuff([time], 3, 0, ':')), 7), 131)

  From #temp

  Edit: sorry, posted the wrong version.

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs