Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

SQL DATE Format

by suresh

In this article, we will show you, How to perform SQL DATE Format or formatting SQL Server Date and Time with an example.

Convert SQL DATE Format Example

Before we go toward the practical example, let me explain to you the available list of Convert date format in Sql Server. For this demonstration, we are going to write different SQL Date format Queries using CONVERT, and FORMAT function. The CONVERT function provides different formatting styles to format date and time.

Without Century (yy)With Century (yyyy)Input/OutputStandard
–0 to 100mon dd yyyy hh:miAM (or PM)This is the Default for both datetime, and smalldatetime
11011 = mm/dd/yy
101 = mm/dd/yyyy
U.S.
21022 = yy.mm.dd
102 = yyyy.mm.dd
ANSI
31033 = dd/mm/yy
103 = dd/mm/yyyy
British/French
41044 = dd.mm.yy
104 = dd.mm.yyyy
German
51055 = dd-mm-yy
105 = dd-mm-yyyy
Italian
61066 = dd mon yy
106 = dd mon yyyy
–
71077 = Mon dd, yy
107 = Mon dd, yyyy
–
8108hh:mi:ss–
–9 or 109mon dd yyyy hh:mi:ss:mmmmAM (or PM)Default + milliseconds
1011010 = mm-dd-yy
110 = mm-dd-yyyy
USA
1111111 = yy/mm/dd
111 = yyyy/mm/dd
JAPAN
1211212 = yymmdd
112 = yyyymmdd
ISO
–13 or 113dd mon yyyy hh:mi:ss:mmm(24h)Europe Default + millisecond
14114hh:mi:ss:mmm(24h)–
–20 or 120yyyy-mm-dd hh:mi:ss(24h)ODBC canonical
–21 or 121yyyy-mm-dd hh:mi:ss.mmm(24h)ODBC canonical with milliseconds. This is the Default for time, date, datetime2, and datetimeoffset
–126yyyy-mm-ddThh:mi:ss.mmm (no Spaces)ISO8601
–127yyyy-mm-ddThh:mi:ss.mmmZ (no Spaces)ISO8601 with time zone Z
–130dd mon yyyy hh:mi:ss:mmmAMHijri
–131dd/mm/yyyy hh:mi:ss:mmmAMHijri

SQL Convert DATE Format Example

In this SQL example we are going to use the CONVERT function on GETDATE() to return the date in different formats.

--Using CONVERT Function for SQL Server Date Format
SELECT CONVERT(VARCHAR(20), GETDATE()) AS 'Result 1';
SELECT CONVERT(VARCHAR(20), GETDATE(), 0) AS 'Result 2';
SELECT CONVERT(VARCHAR(20), GETDATE(), 100) AS 'Result 3';

SELECT CONVERT(VARCHAR(20), GETDATE(), 1) AS 'Result 4';
SELECT CONVERT(VARCHAR(20), GETDATE(), 101) AS 'Result 5';

SELECT CONVERT(VARCHAR(20), GETDATE(), 2) AS 'Result 6';
SELECT CONVERT(VARCHAR(20), GETDATE(), 102) AS 'Result 7';
SQL DATE Format 1

Convert SQL DATE Format Example 2

This example query covers few more date formats in Convert function

--Using CONVERT Function for SQL Server Date Format
SELECT CONVERT(VARCHAR(20), GETDATE(), 3) AS 'Result 8';
SELECT CONVERT(VARCHAR(20), GETDATE(), 103) AS 'Result 9';

SELECT CONVERT(VARCHAR(20), GETDATE(), 4) AS 'Result 10';
SELECT CONVERT(VARCHAR(20), GETDATE(), 104) AS 'Result 11';

SELECT CONVERT(VARCHAR(20), GETDATE(), 5) AS 'Result 12';
SELECT CONVERT(VARCHAR(20), GETDATE(), 105) AS 'Result 13';

SELECT CONVERT(VARCHAR(20), GETDATE(), 6) AS 'Result 14';
SELECT CONVERT(VARCHAR(20), GETDATE(), 106) AS 'Result 15';
SQL DATE Format 2

In this example query, we are showing few other date formats in Convert function are

--Using CONVERT Function for SQL Server Date Format
SELECT CONVERT(VARCHAR(20), GETDATE(), 7) AS 'Result 16';
SELECT CONVERT(VARCHAR(20), GETDATE(), 107) AS 'Result 17';

SELECT CONVERT(VARCHAR(20), GETDATE(), 8) AS 'Result 18';
SELECT CONVERT(VARCHAR(20), GETDATE(), 108) AS 'Result 19';

SELECT CONVERT(VARCHAR(20), GETDATE(), 9) AS 'Result 20';
SELECT CONVERT(VARCHAR(20), GETDATE(), 109) AS 'Result 21';

SELECT CONVERT(VARCHAR(20), GETDATE(), 10) AS 'Result 22';
SELECT CONVERT(VARCHAR(20), GETDATE(), 110) AS 'Result 23';
SQL DATE Format 3

Some other convert date format

--Using CONVERT Function for SQL Server Date Format
--Using CONVERT Function for SQL Server Date Format
SELECT CONVERT(VARCHAR(20), GETDATE(), 11) AS 'Result 24';
SELECT CONVERT(VARCHAR(20), GETDATE(), 111) AS 'Result 25';

SELECT CONVERT(VARCHAR(20), GETDATE(), 12) AS 'Result 26';
SELECT CONVERT(VARCHAR(20), GETDATE(), 112) AS 'Result 27';

SELECT CONVERT(VARCHAR(20), GETDATE(), 113) AS 'Result 28';

SELECT CONVERT(VARCHAR(20), GETDATE(), 14) AS 'Result 29';
SELECT CONVERT(VARCHAR(20), GETDATE(), 114) AS 'Result 30';

SELECT CONVERT(VARCHAR(20), GETDATE(), 20) AS 'Result 31';
SQL DATE Format 4

The following are the list of remaining formatting options available in convert function for date format.

--Using CONVERT to Format Date in SQL Server
SELECT CONVERT(VARCHAR(35), GETDATE(), 21) AS 'Result 32';

SELECT CONVERT(VARCHAR(35), GETDATE(), 126) AS 'Result 33';

SELECT CONVERT(VARCHAR(35), GETDATE(), 127) AS 'Result 34';

SELECT CONVERT(NVARCHAR(35), GETDATE(), 130) AS 'Result 35';

SELECT CONVERT(NVARCHAR(35), GETDATE(), 131) AS 'Result 36';
SQL DATE Format 5

SQL Date Format using FORMAT Function

In this example, we are going to use the FORMAT function on GETDATE() to return the date in different formats. I suggest you refer the Standard Date and Time Format Strings to understand the string formats that we used in this example.

--Using FORMAT Function for SQL Server Date Format
SELECT FORMAT(GETDATE(), 'd', 'en-US' ) AS 'Result 1'
SELECT FORMAT(GETDATE(), 'D', 'en-US' ) AS 'Result 2'
SELECT FORMAT(GETDATE(), 'f', 'en-US' ) AS 'Result 3'
SELECT FORMAT(GETDATE(), 'F', 'en-US' ) AS 'Result 4'
SELECT FORMAT(GETDATE(), 'g', 'en-US' ) AS 'Result 5'
SELECT FORMAT(GETDATE(), 'G', 'en-US' ) AS 'Result 6'
SELECT FORMAT(GETDATE(), 'm', 'en-US' ) AS 'Result 7'
SELECT FORMAT(GETDATE(), 'M', 'en-US' ) AS 'Result 8'
SQL DATE Format 6

The following are the list of remaining formats that are available in format function for date format.

-- Using FORMAT Function for SQL Server Date Format
SELECT FORMAT(GETDATE(), 'O', 'en-US' ) AS 'Result 9'
SELECT FORMAT(GETDATE(), 'R', 'en-US' ) AS 'Result 10'
SELECT FORMAT(GETDATE(), 's', 'en-US' ) AS 'Result 11'
SELECT FORMAT(GETDATE(), 'S', 'en-US' ) AS 'Result 12'
SELECT FORMAT(GETDATE(), 't', 'en-US' ) AS 'Result 13'
SELECT FORMAT(GETDATE(), 'T', 'en-US' ) AS 'Result 14'
SELECT FORMAT(GETDATE(), 'u', 'en-US' ) AS 'Result 15'
SELECT FORMAT(GETDATE(), 'U', 'en-US' ) AS 'Result 16'
SELECT FORMAT(GETDATE(), 'Y', 'en-US' ) AS 'Result 17'
SQL DATE Format 7

Date Format using Datetime Functions

Here, we are going to use the DATE and TIME functions that are available to format date and time in SQL Server to return the date in different formats.

--Using Different Date and Time functions for SQL Server Date Format
SELECT DATEFROMPARTS(2017, 06, 14) AS 'Result 1';

SELECT DATETIMEFROMPARTS(2017, 06, 14, 11, 57, 53, 847) AS 'Result 3';

SELECT EOMONTH('20170614') AS 'Result 3';
SQL DATE Format 8

Date Format using Conversion Functions

In this example, we are going to use the Sql Server Conversion Functions to format the date. And the Conversation functions are PARSE, TRY_PARSE, CONVERT, and TRY_CONVERT. We use these functions are different dates to return the date in different formats.

--Using Different Conversion functions for SQL Server Date Format
SELECT PARSE('Wednesday, June 14, 2017' AS DATETIME USING 'en-US') AS 'Result 4'; 

SELECT TRY_PARSE('Wednesday, June 14, 2017' AS DATETIME USING 'en-US') AS 'Result 5'; 

SELECT CONVERT(DATETIME2, SYSDATETIME()) AS 'Result 6'; 

SELECT TRY_CONVERT(DATETIME2, '6/14/2017 11:57:53 AM') AS 'Result 7';
SQL DATE Format 9

Users who want to save the date Formats provided by CONVERT function in your mobiles can use the following image:

SQL DATE Format 0

Placed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW

Copyright © 2021· All Rights Reserved.
About | Contact | Privacy Policy