use sybsystemprocs
go
drop procedure sp_date
go
drop procedure sp_duration
go
drop procedure sp_version
go

create procedure sp_date as
   set flushmessage                  on
   set string_rtruncation            on

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_date> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   if (datepart (hour, getdate ()) < 12)
      begin
         select 'CURRENT DATE/TIME' = convert (varchar (12), getdate (), 107)
                                      + ' ('
                                      + convert (char (8), getdate (), 8)
                                      + ' AM)'
      end
   else
      begin
         select 'CURRENT DATE/TIME' = convert (varchar (12), getdate (), 107)
                                      + ' ('
                                      + convert (char (8), getdate (), 8)
                                      + ' PM)'
      end
go
sp_addcomment sp_date, 1, 'Prints current date/time in Mon DD, CCYY (HH:MM:SS) format'
go
sp_procxmode sp_date, anymode
go

create procedure sp_duration (@TIMESTART  datetime,
                              @TIMEEND    datetime,
                              @DURATION   varchar (14) output) as
   --
   --  RETURN A DURATION IN AN APPROPRIATE DATA-DEPENDENT FORMAT.  ALL
   --  BUT THE FIRST NUMBER WILL BE LEFT ZERO-PADDED.  NOTE THAT THE
   --  MILLISECONDS VALUE IS SOMETIMES SHORT BY 2 MS.
   --     HH:MM:SS
   --     MM:SS
   --     SS.MSS seconds
   --     MSS ms.
   --
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @hh  int,
           @mm  int,
           @ms  int,
           @ss  int

   select @ms       = datediff (ms, @TIMESTART, @TIMEEND),
          @DURATION = NULL

   select @ss = @ms /1000,
          @ms = @ms % 1000

   if (@ss > 0)
      begin
         select @mm = @ss / 60,
                @ss = @ss % 60

         if (@mm > 0)
            begin
               if (@ss > 9)
                  begin
                     select @DURATION = convert (char (2), @ss)
                  end
               else
                  begin
                     select @DURATION = '0'  +  convert (char (1), @ss)
                  end

               select @hh = @mm / 60,
                      @mm = @mm % 60

               if (@mm > 9)
                  begin
                     select @DURATION = convert (char (2), @mm)  +  ':'  +  @DURATION
                  end
               else
                  begin
                     select @DURATION = convert (char (1), @mm)  +  ':'  +  @DURATION
                  end

               if (@hh > 0)
                  begin
                     select @DURATION = convert (varchar (2), @hh)  +  ':'  +  @DURATION
                  end
            end
         else
            begin
               if (@ms > 99)
                  begin
                     select @DURATION = '.'  +  convert (char (3), @ms)
                  end
               else if (@ms > 9)
                  begin
                     select @DURATION = '.0'  +  convert (char (2), @ms)
                  end
               else
                  begin
                     select @DURATION = '.00'  +  convert (char (1), @ms)
                  end

               select @DURATION = convert (varchar (2), @ss)  +  @DURATION  +  ' seconds'
            end
      end
   else
      begin
         select @DURATION = convert (varchar (4), @ms)  +  ' ms.'
      end
go
sp_addcomment sp_duration, 1, 'Returns DURATION = TIMEEND - TIMESTART'
go
sp_procxmode sp_duration, anymode
go

create procedure sp_version as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_version> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @break  tinyint

   select @break = charindex (' ', substring (@@version, 45, 255))

      select FIELD = space (9),
             VALUE = space (67)
      where 1 = 2
   union
      select 'Date Run:',
             convert (varchar (12), getdate (), 107)
                + ' ('
                + convert (char (8), getdate (), 8)
                + ' AM)'
      where datepart (hour, getdate ()) < 12
   union
      select 'Date Run:',
             convert (varchar (12), getdate (), 107)
                + ' ('
                + convert (char (8), getdate (), 8)
                + ' PM)'
      where datepart (hour, getdate ()) > 11
   union
      select 'Server:',
             substring (@@servername, 1, 67)
   union
      select 'Version:',
             substring (substring (@@version, 1, 44 + @break), 1, 67)
   union
      select '~        ',
             substring (ltrim (substring (@@version, 45 + @break, 255)), 1, 67)
   order by FIELD
go
sp_addcomment sp_version, 1, 'Prints current date, SQL Server name, and SQL Server version'
go
sp_procxmode sp_version, anymode
go

dump transaction sybsystemprocs with truncate_only
go
