SQL Snippet: Format Dates

If you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.

This second example, shows how to format a date in two of the most common formats I work with. Each example returns the date using the FORMAT command introduced in SQL Server 2012 and the more traditional method.

The first example, returns the date as day month year separated with /:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
DECLARE @DATE DATETIME = '2017-05-31 11:59:59.000'

SELECT
	CONVERT(VARCHAR(10), @DATE, 103)
	,FORMAT(@DATE, 'dd/MM/yyyy')

The second returns the date in ISO 8601 format:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
DECLARE @DATE DATETIME = '2017-05-31 11:59:59.000'

SELECT
	CONVERT(VARCHAR(10), @DATE, 126)
	,FORMAT(@DATE, 'yyyy-MM-dd')