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.