A very nice utilitarian date formatting function that mimics VB's date format strings is available at http://www.sqlservercentral.com/scripts/contributions/1007.asp

I highly recommend it for low transaction volume systems that have odd date reformatting requirements rather than reinventing the wheel for each new format.

#1. This function has a bug (or issue)

select dbo.fn_FormatDate (getdate(), 'xx:ss') will result 'xx:17'

It is not a date format. Function required format verification code.

#2. If every programmer will start writing format every time - it will end up with many errors. We have 5000 - 6000 reports and 200-300 applications. Each Developer developping 1 application every 3-5 weeks. It is more safe to make the predefined formats. Espesially, when number of formats are limited.

#3. I am not pretending to get the best solutions - the article just shows some WAYS of datatime data type usage. It is like to say that the person who shows how engine is working is not presenting the best engine design. This is not the purpose of the article.

Thanks for the responce.

If my post was taken as critical I apologize. I was just trying to offer the script from the library as an example of a flexible way to provide date formatting functionality. The author acknowedges there are issues, but corrections have been posted in the past and are welcome by the author.

It seems to me (IMHO) that providing a comprehensive date formatting solution that is implemented such that it is available to all developers for all databases is a good way to ensure uniformity of both development techniques and output formatting.

Again, this was not intended as criticism, just offering an opinion. I rated your article as "good" so that should speak for my opinion.

Thanks for email. In all my articles I am trying to show some ideas and provide with the example of the code (not necessary the best one but working). And always I am glad when a person takes an advice and use it based on the situation. Each situation may post a challenge and may need to be resolved its own way.

Again, thanks.

Both are good examples of some scripts.  Does anyone have an addition to the UDF_CONVERTDATE that would add option 7 that would allow returning just the time?  Maybe 7 would return "1:30 PM" and 8 would return "13:30".  Just thought I'd ask before trying to add it myself.

Does SQL 2005 have some of this built in?

In reality, I have about 15 different formats. The one you asking looks:

IF (@dateformat = 8)
 begin

 set @converteddt =
  RIGHT('0' + CAST(DATEPART ( hh , @datevar ) AS VARCHAR(2)), 2) +  ':' +
  RIGHT('0' + CAST(DATEPART ( mi , @datevar ) AS VARCHAR(2)), 2)
  
  set @formatexists = 1
 end

I don't have #7 but it can be done similar way with CASE statement or conversion of datetime variable to varchar first based on the formatting parameter.

I can make it Monday. I don't have SQL Server installed on my computer now.

Formats you need based on your posted message:

IF (@dateformat = 7)
 begin

 set @converteddt =
  RIGHT('0' + right( convert(varchar, getdate(),100 ) ,7), 7)

  set @formatexists = 1
  end

IF (@dateformat = 8)
 begin

 set @converteddt =
  RIGHT('0' + CAST(DATEPART ( hh , @datevar ) AS VARCHAR(2)), 2) +  ':' +
  RIGHT('0' + CAST(DATEPART ( mi , @datevar ) AS VARCHAR(2)), 2)
  
  set @formatexists = 1
 end

Oops, need change getdate to @datevar

IF (@dateformat = 7)
 begin

 set @converteddt =
  RIGHT('0' + right( convert(varchar, @datevar,100 ) ,7), 7)

  set @formatexists = 1
  end

IF (@dateformat = 8)
 begin

 set @converteddt =
  RIGHT('0' + CAST(DATEPART ( hh , @datevar ) AS VARCHAR(2)), 2) +  ':' +
  RIGHT('0' + CAST(DATEPART ( mi , @datevar ) AS VARCHAR(2)), 2)
  
  set @formatexists = 1
 end

Thanks for the replies.  That is helpful.  The AM/PM doesn't use a space after the time, so I revised as below.  Not sure why the '0' + was added since it was always removed by the outer function.

IF (@dateformat = 7)
 begin

 set @converteddt =
  STUFF(right( convert(varchar,@datevar,100 ) ,7), 6, 0, ' ')

  set @formatexists = 1
  end