convert seconds and if exists stored proc not create it

• fasttrack

  SSCertifiable

  Points: 6071

  More actions

  March 9, 2006 at 9:21 am

  #111435

  Hi everybody:

  I have 2 questions for you:

  How can I trasform second to: HH:mm:ss ?

  5400 secs --> 01:30:00 (1 hour and 30 mins)

  And the second one:

  To create a stored proc I use this TSql

  IF EXISTS (SELECT name FROM   sysobjects WHERE  name = N'proctest' AND    type = 'P')

      DROP PROCEDURE proctest

  GO

  CREATE PROCEDURE proctest

  AS

  GO

  But in the case:

  If there is the store proc, the stored proc is NOT installed, if missed must be installed, how can I write the code?

  Thank a lot....

  

• Phil Parkin

  SSC Guru

  Points: 247039

  More actions

  March 9, 2006 at 9:52 am

  #625745

  Try

  select convert(char(12),dateadd(s, 5400, 0),114)

  for your conversion question.

  Your second question - just use

  IF NOT EXISTS (select procedure entry here) CREATE PROCEDURE

  ---

• fasttrack

  SSCertifiable

  Points: 6071

  More actions

  March 9, 2006 at 10:14 am

  #625749

  Thank... here another solution (for everybody) , but yours one is faster....

  DECLARE @sd INT

   

   

  SET @sd = 5400

   

  SELECT [HH:MM:SS] = 

      CASE WHEN @sd/3600<10 THEN '0' ELSE '' END

      + RTRIM(@sd/3600)

      + ':' + RIGHT('0'+RTRIM((@sd % 3600) / 60),2)

      + ':' + RIGHT('0'+RTRIM((@sd % 3600) % 60),2)

   

  for the second question

  I made the same but I get an error, and I would like to avoid it

  any solution will be very appreciated..

  thank

• Mark Harr

  SSCrazy Eights

  Points: 9921

  More actions

  March 9, 2006 at 10:39 am

  #625760

  The Convert version from Phil will only work for seconds < 86400 (nbr second in a 24-hour day).  If it is possible to have a greater value, you will want to use the more complex version.

  
  
  Mark

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  March 9, 2006 at 1:28 pm

  #625791

  IF NOT EXISTS (select procedure entry here)

  EXEC('CREATE PROCEDURE ....')

  _____________
  Code for TallyGenerator

• fasttrack

  SSCertifiable

  Points: 6071

  More actions

  March 9, 2006 at 3:32 pm

  #625821

  Thank to everybody for your useful suggestion.

  I'll try

  IF NOT EXISTS (select procedure entry here)

  EXEC('CREATE PROCEDURE ....') ............................

  thank