SELECT CONVERT(VARCHAR(20), GETDATE(), 105) AS 'Result 13' SELECT CONVERT(VARCHAR(20), GETDATE(), 5) AS 'Result 12' SELECT CONVERT(VARCHAR(20), GETDATE(), 104) AS 'Result 11' SELECT CONVERT(VARCHAR(20), GETDATE(), 4) AS 'Result 10' SELECT CONVERT(VARCHAR(20), GETDATE(), 103) AS 'Result 9' SELECT CONVERT(VARCHAR(20), GETDATE(), 3) AS 'Result 8' This example query covers few more date formats in Convert function -Using CONVERT Function for SQL Server Date Format SELECT CONVERT(VARCHAR(20), GETDATE(), 102) AS 'Result 7' Convert SQL DATE Format Example 2 SELECT CONVERT(VARCHAR(20), GETDATE(), 2) AS 'Result 6' SELECT CONVERT(VARCHAR(20), GETDATE(), 101) AS 'Result 5' SELECT CONVERT(VARCHAR(20), GETDATE(), 1) AS 'Result 4' SELECT CONVERT(VARCHAR(20), GETDATE(), 100) AS 'Result 3' SELECT CONVERT(VARCHAR(20), GETDATE(), 0) AS 'Result 2' SELECT CONVERT(VARCHAR(20), GETDATE()) AS 'Result 1' Using CONVERT Function for SQL Server Date Format
In this SQL example we are going to use the CONVERT function on GETDATE() to return the date in different formats.
This is the Default for time, date, datetime2, and datetimeoffset – 126 yyyy-mm-ddThh:mi:ss.mmm (no Spaces) ISO8601 – 127 yyyy-mm-ddThh:mi:ss.mmmZ (no Spaces) ISO8601 with time zone Z – 130 dd mon yyyy hh:mi:ss:mmmAM Hijri – 131 dd/mm/yyyy hh:mi:ss:mmmAM Hijri SQL Convert DATE Format Example
#SQL PROMPT INTERNAL TIME FORMAT ISO#
Without Century (yy) With Century (yyyy) Input/Output Standard – 0 to 100 mon dd yyyy hh:miAM (or PM) This is the Default for both datetime, and smalldatetime 1 101 1 = mm/dd/yyġ03 = dd/mm/yyyy British/French 4 104 4 = dd.mm.yyġ04 = dd.mm.yyyy German 5 105 5 = dd-mm-yyġ05 = dd-mm-yyyy Italian 6 106 6 = dd mon yyġ07 = Mon dd, yyyy – 8 108 hh:mi:ss – – 9 or 109 mon dd yyyy hh:mi:ss:mmmmAM (or PM) Default + milliseconds 10 110 10 = mm-dd-yyġ10 = mm-dd-yyyy USA 11 111 11 = yy/mm/ddġ11 = yyyy/mm/dd JAPAN 12 112 12 = yymmddġ12 = yyyymmdd ISO – 13 or 113 dd mon yyyy hh:mi:ss:mmm(24h) Europe Default + millisecond 14 114 hh:mi:ss:mmm(24h) – – 20 or 120 yyyy-mm-dd hh:mi:ss(24h) ODBC canonical – 21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h) ODBC canonical with milliseconds. The CONVERT function provides different formatting styles to format date and time. For this demonstration, we are going to write different SQL Date format Queries using CONVERT, and FORMAT function. Convert SQL DATE Format Exampleīefore we go toward the practical example, let me explain to you the available list of Convert date format in Sql Server.
#SQL PROMPT INTERNAL TIME FORMAT HOW TO#
In this article, we will show you, How to perform SQL DATE Format or formatting SQL Server Date and Time with an example.