sql-servert-sql

Change date format in SQL Table

My SQL table shows dates in US format. I would like to change them to show UK format in the table.

My column datatype is smalldatetime.

Existing SQL table format yyyy-MM-dd mm:ss

enter image description here

I would like the format to show yyyy-dd-MM mm:ss in the table.

Is there anyway I can bulk change the formatting for each row to yyyy-dd-MM mm:ss?

Solution

  • Since column type is smalldatetime, basically the first thing you need to do is to check the docs. Dates are stored in binary format, that is, the actual data is agnostic to locale and the format is defined at display level.

    Date format can be changed to the one you prefer, as described here.

    They give us this example with variables:

    -- Set date format to day/month/year.  
SET DATEFORMAT dmy;  
GO  
DECLARE @datevar DATETIME2 = '31/12/2008 09:01:01.1234567';  
SELECT @datevar;  
GO  
-- Result: 2008-12-31 09:01:01.123  
SET DATEFORMAT dmy;  
GO  
DECLARE @datevar DATETIME2 = '12/31/2008 09:01:01.1234567';  
SELECT @datevar;  
GO  
-- Result: Msg 241: Conversion failed when converting date and/or time -- from character string.  
  
GO

    You can also use the FORMAT function:

    SELECT FORMAT (getdate(), 'yyyy-dd-MM') as date

    and you can apply the same function to the field when you select it. Alternatively you can apply your formatting at app code level, depending on what is the most feasible for you.

    As pointed out in the comment-section, FORMAT may be slow (I'm not an active user of SQL Server since 2020, except for a brief task last month, so I did not remember what was quick and what was slow), the use of the convert function is recommended to provide similarly good results, but with better performance.

    More information can be found here.