How to - SQL Format Date and Format Datetime using T-SQL Convert Function
SQL developers and SQL Server database administrators frequently convert datetime to string in sql codes using t-sql Convert function.
The sql Convert function is a powerful conversion function with its parameters.
One of the parameter of the sql format date function "Convert" is the style parameter.
The style parameter defines the output string format of the date/datetime value expressed as string value.
But since there exists about 40 different datetime formats, and since sql programmers generally deal with sql format date and format datetime tasks with different formats, it is difficult to memorize and keep in mind the style parameter value of every sql date format option.
Let's remember the t-sql convert datetime function syntax once more :
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
--T-sql convert datetime function example usage code to format date mm-dd-yyyy
SELECT CONVERT(nvarchar(30),getdate(),110)
In many sql forums, I generally see the question, "How to format date" and "How to format datetime" values in SQL Server.
I had build a simple sql helper procedure hoping to be useful for sql programmers to answer their questions on datetime convert problems and date formats.
CREATE PROC SQLFormatDate
AS
SET NOCOUNT ON
CREATE TABLE #tt (
id INT IDENTITY(1,1),
datestring NVARCHAR(30),
style INT
)
DECLARE @style INT = 1
DECLARE @sql NVARCHAR(100)
WHILE @style < 150
BEGIN
SET @sql = 'SELECT CONVERT(nvarchar(100),getdate(),' + cast(@style as nvarchar(5))+ ')'
BEGIN TRY
INSERT INTO #tt(datestring) EXEC SP_EXECUTESQL @sql;
UPDATE #tt SET style = @style WHERE id = @@IDENTITY;
END TRY
BEGIN CATCH
END CATCH;
SET @style = @style + 1;
END;
SELECT
datestring,
style,
'SELECT CONVERT(nvarchar(100),getdate(),' + cast(style as nvarchar(5))+ ')' example
FROM #tt
DROP TABLE #tt
SET NOCOUNT OFF
GO
SQL developers can execute the above helper sql convert datetime procedure to see how can they get use of it in order to use in sql format datetime to string.
All you have to do is after creating the above SQLFormatDate procedure, execute the below t-sql sp call statement.
EXEC SQLFormatDate
If you set the Microsoft SQL Server Management Studio output of t-sql queries as text by just following on the menu, "Query > Results to > Results to Text" or simply type the short-cut key combination Ctrl+T to display the results of the stored procedure call SQLFormatDate as text.
This will enable you to search among the output formats since the output is in single window and is in text format.
For example if you want to sql format date and datetime variables using t-sql Convert function in the dd/mm/yyyy format, you can search the today 's datetime format value in dd/mm/yyyy which is 11/01/2010.
And when you search for the dd/mm/yyyy which has a value of 11/01/2010, you will see that the corresponding t-sql Convert function style parameter is equal to 103.
Also you can find a sample sql query, select statement "SELECT CONVERT(nvarchar(100),getdate(),103)" that you can test the tsql datetime format convert function to execute on the SQL Server Management Studio.
Note that the output of the format datetime sql procedure SQLFormatDate shows all date formats available for sql developers in one single formatting date function.
But still t-sql developers can require more complex date formats for their string datetime expressions.
For example if the tsql format string date requirement is "dd-mon-yyyy" date format which is pretty similar to datestring output of the Convert function with style parameter equal to 106.
So just copy the related example column of the SQLFormatDate ad make some modifications.
SELECT REPLACE(CONVERT(nvarchar(30),getdate(),106), ' ', '-')
And the output of the above convert string datetime expression is just as we desired : 11-Jan-2010 (dd-Mon-yyyy)
I hope you got the idea on how sql stored procedure SQLFormatDate can help to format string date and datetime values on target sql date formats.
An other example will also answer the common question of sql developers : How to format date column as YYYY-MM-DD ?
Actually when you executhe sql convert date help stored procedure "", you can see that the style parameter 23 will exactly solve our problem.
-- format date column in YYYY-MM-DD date format
SELECT CONVERT(nvarchar(30),getdate(),23)
An other way of formatting date column in YYYY-MM-DD date format using sql Convert function is converting or to cast date column to string using the convert sql function with style parameter equal to 120.
Then applying the sql LEFT string function or applying sql SUBSTRING string function as shown in the below example tsql codes will definitely give results in desired date formats YYYY-MM-DD .
SELECT LEFT(CONVERT(nvarchar(30),getdate(),120),10)
SELECT SUBSTRING(CONVERT(nvarchar(30),getdate(),120),1,10)
For t-sql developers or SQL Server database administrators (DBS 's), the above SQLFormatDate stored procedure can be useful to see all the available formats with the current datetime value.
I believe, to see all available sql format date options will make sql developers work easier and faster to finish datetime convert problems.