Globally change Date Format

• webskater

  SSCarpal Tunnel

  Points: 4214

  More actions

  September 6, 2010 at 7:16 am

  #229065

  I have been asked how easy it is to change the date format presented to users. It's a sizeable database with over a thousand stored procedures and the vast majority of them reference at least one (and up to about 20) date fields.

  So, within SELECT statements in the stored procedures, something like this ...

  CONVERT(VARCHAR(10), SomeFieldName, 103) AS [SomeFieldName]

  must appear, in total, maybe 5,000 times.

  Is there a way of globally changing that date format from a 103 to a 106?

  Thanks for any help.

• Abhijit More

  SSCertifiable

  Points: 6897

  More actions

  September 6, 2010 at 8:16 am

  #1216949

  I think you can use DATEFORMAT. it overrides the implicit date format settings.

  Abhijit - http://abhijitmore.wordpress.com

• Madhivanan-208264

  SSCertifiable

  Points: 7516

  More actions

  September 7, 2010 at 6:57 am

  #1217293

  Also if you want to show formatted dates in any front end application, do formation there. Otherwise to simplify the code, use a derived table

  select mycol from

  (

  select *,convert(varchar(20),mydatecol,103) as mycol

  ) as t

  This way you may need to change only in few places

  ---

  Madhivanan

  Failing to plan is Planning to fail