Time Scalar Function

Everyone knows that you can use the Convert DML to convert a DateTime data-type to a string evaluation for either split storage or display to an consuming interface.

As most of us use the Date portion of a DateTime value for 90% of everything we are rendering the data to.  But what about that 10% where we either need to split render the Date & Time seperately.  After much review, we only have two quick and easy values to use and neither are really interchangeable.

Option #1, with 2 forms:

 select convert(varchar(10), getdate(), 108)  
 -- Produces: 13:53:15  

 select convert(varchar(15), getdate(), 114)  
 -- Produces: 13:53:15:707  

Now when using these forms of conversion, about 75% you have met the need for your requirement.  But lets say you need to use the Time string created for say a Filename ( sometext_[[TIME]].txt ), what do you do?

Well the next code segment is what will be produced in order meet the FileName convention of special characters.

 select replace(convert(varchar(10), getdate(), 108), ':', '')  
 -- Produces: 135922  

 select replace(convert(varchar(15), getdate(), 114), ':', '')  
 -- Produces: 135922253  

Now on to the other situations, as you can see we have two solutions that will meet either 90% of your needs or 75% of your needs.  In those situations where you need to also meet the left over 10% or 25%, respectively, what do you do?  You can either create some custom code that skims the produced string value for further modifications or you could use the DML function attached to SQL to produce the format that you will need 100% of the time for either situation.  It will even accomidate the Date forms needed.

So this 100% solution is called Format.  For us transient developers, that bounce between .Net and SQL development interchangeably for various projects, the function signature is very familiar.  Not as expansive as the .Net form but I will take the fact that it does not allow for Nano-second inclusion over the Convert method with limited returns.

Enter Scalar-UDF implementation
The current form being posted is not very flexible, in so far as I had a need that the Convert evaluation could not accomidate and allows you to bypass the Server DateTime evaluation setting.

 ALTER FUNCTION [Schema].[f_Get_TimeFormat] (  
      @dt datetime,  
      @format varchar(25) = '24mmss'  
 ) RETURNS varchar(25)  
 AS  
 BEGIN  
      if(left(@format, 2) = '12') begin  
           set @format = replace(@format, '12', 'hh')  
           set @format = @format + 'tt'  
      end  

      if(left(@format, 2) = '24') begin  
           set @format = replace(@format, '24', 'HH')  
      end  

      set @format = replace(@format, 'MM', 'mm')  
      set @format = replace(@format, 'SS', 'ss')  

      -- Return the result of the function  
      RETURN Format(@dt, @format)  
 END  

I realize its not much, in terms of logic and implementation.  But as stated previously, it is currently in its "Infant" stage of development.  It fit the current need but I tried to keep it as flexible as possible so any future need or situation would be easily implemented into the body with little to no effect on previous implementations.

 -- OLD  
 select replace(convert(varchar(10), getdate(), 108), ':', '')  
 -- Produces: 141901  

 /**** NEW ****/
 select Schema.f_Get_TimeFormat(getdate(), '24MMSS')  
 -- Produces: 141901  

 select Schema.f_Get_TimeFormat(getdate(), '12MMSS')
 -- Produces: 021901PM