SQL – Update Date to UK Format

By David Crowther

Question:

How can you update a date value in SQL to UK Format – dd/mm/yyyy?

Answer:

If you have a field in a SQL database that stores a date value – it will likely use this format yyyy-mm-dd

The field type for this field will be date –

Notice that the values are stored in a yyyy-mm-dd format.

However, if you wish to present this information back to the users in a more readable format e.g., UK date format dd/mm/yyyy, you can create a trigger in the database that updates a new text field with the UK date format.

1 – Create the new Date field in the Design of your SQL table as text format – e.g., nvarchar(50)

2 – Create a Trigger on the table, that runs on insert and update –

The trigger will update the new Date_Text field with the values from the date field, but it will convert them on the fly to a textual version of the date.

Update [dbo].[TABLE] set Contract_Expiry_Date_Text = CONVERT(varchar(10), Contract_Expiry_Date, 103)

The number 103 on the end is the style code for UK date format – dd/mm/yyyy

3 – Now when the table is updated e.g., a new record is created, and a Date value chosen by the user…

Once saved, the record is inserted with the date value in the usual date format – yyyy-mm-dd

..but then the trigger will auto populate the date text field with the UK date style – dd/mm/yyyy

And so, in your system e.g., GIS the UK date can be shown as a more readable label.