• Blog posts

    Bio

    By David Crowther

    SQL – Update Date to UK Format - 1.png

    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

    SQL – Update Date to UK Format - 2.png

    The field type for this field will be date

    SQL – Update Date to UK Format - 3.png

    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)

    SQL – Update Date to UK Format - 4.png

    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

    SQL – Update Date to UK Format - 5.png

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

    SQL – Update Date to UK Format - 6.png

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

    SQL – Update Date to UK Format - 7.png

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

    SQL – Update Date to UK Format - 8.png

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

    SQL – Update Date to UK Format - 9.png

Comments

0 comments

Please sign in to leave a comment.