SQL Server Date Format [10 Amazing Examples] - DatabaseFAQs.com (2024)

This SQL Servertutorial will teach us how SQL Server Date Format dd mm yyyy works. Moreover, we will discuss and learn some examples so that you can understand the concept much better. Here is the complete list of topics that we will cover.

• SQL Server Date Format mm/dd/yyyy
• SQL Server Date Format mm/dd/yyyy hh:mm:ss
• SQL Server Date Format mm/dd/yyyy
• SQL Server Date Format mm/dd/yyyy Convert
• SQL Server 2008 Format Date mm/dd/yyyy
• SQL Server Date Format mm/dd/yyyy hh:mm:ss am
• SQL Server Default Date Format dd/mm/yyyy
• SQL Server Select Date Format dd/mm/yyyy hh:mm
• SQL Server Regular Expression for Date Format mm/dd/yyyy
• SQL Server Date Format dd/mm/yyyy to yyyy-mm-dd

SQL Server Date Format mm dd yyyy

In this SQL Server section, we will learn how to use the FORMAT function on the DATE FORMAT column of a table by the query. And which will be explained with the help of an illustrated example.

With the help of the SQL Server FORMAT function, we don’t need to remember and know the FORMAT NUMBER which will be used and get the proper date format that we want. We can just specify the date format we want and get the format. The FORMAT function is used to format dates and time data types of a column (datetime, datetime2, date, smalldatetime, datetimeoffset, etc) from a table.

• If we want to have DD/MM/YYYY then we will use the SELECT FORMAT(getdate(), ‘dd/MM/yyyy ‘) as a date.
• To get the format as MM-DD-YY then we will use the SELECT FORMAT(getdate(),’ MM-DD-YY’) as a date.

As we can see a lot of options for the date and time formatting are given below:

• DD– the day of the month starts from 01 to 31.
• dddd– In this way, the day is spelled out.
• MMM– It’s used for a month name abbreviation.
• MM– the Month number starting from 01 to 12.
• yy– It gives the year’s last two-digit number.
• MMMM– This is the month that is spelled out.
• yyyy– It gives the year’s last four-digit number.
• hh– it gives hours from 01 to 12.
• mm– It gives the minute value from 00 to 59.
• HH– it gives hours from 00 to 23.
• tt– this shows either AM or PM.
• d– it shows this day of the month from 01 to 31 (if it is used on its own then it will display the entire date).
• ss– It gives the second value from 00 to 59.
• us– this shows the date in the United States of America culture which is MM/DD/YYYY.

Let’s have a look at the syntax of the SQL Server FORMAT function by the following query:

SYNTAX:

SELECT expression, FORMAT(DATE_VALUE, FORMAT) FROM YOUR_TABLE_NAME;

Here is an illustrated example of the FORMAT function to do the date format of a column from a table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;SELECT STUDENT_FIRSTNAME,STUDENT_LASTNAME,FORMAT(STUDENT_ADMITDATE,'MM/DD/YYYY')FROM HARVARD_UNIVERSITY;

In this preceding query, the SELECT statement is used to retrieve all records of the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table.

The FORMAT function is used on the STUDENT_ADMITDATE column for the date formation in the form of MM/dd/yyyy value in the HARVARD_UNIVERSITY table.

We hope that you have understood how to use the SQL Server FORMAT function on a DATE column in the date format of mm/dd/yyyy value from a table by the query. For better elucidation, we have used a sample example and described it in deepness.

Also, check: SQL Server First Day Of Month

SQL Server Date Format mm/dd/yyyy hh:mm:ss

Here we will learn and understand how to use the SQL Server FORMAT function on the date format as mm/dd/yyyy hh:mm: ss in a column of the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;SELECT STUDENT_FIRSTNAME,STUDENT_LASTNAME,FORMAT(STUDENT_ADMITDATE,'MM/dd/yyyy HH:mm:ss') AS DATE_FORMATFROM HARVARD_UNIVERSITY;

As we see in the above query, we have used the SELECT statement to retrieve all records of the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table.

The FORMAT function is used on the STUDENT_ADMITDATE column to give in the date format as MM/dd/yyyy HH:mm: ss in the HARVARD_UNIVERSITY table.

To shorten the function_name, we used the ALIAS clause with the AS keyword and gave the name DATE_FORMAT for the output column_name.

We hope that you have understood the subtopic “SQL Server Date Format mm/dd/yyyy hh:mm: ss” by the above query. For a better explanation, we have used an example and explained it in deepness.

Read: SQL Server Datetime functions examples

SQL Server Date Format mm/dd/yyyy

We will look at an example of the DATE_FORMAT as mm/dd/yyyy by using the SQL Server FORMAT function on the table by the query.

EXAMPLE:

USE SQLSERVERGUIDES;SELECT STUDENT_FIRSTNAME,STUDENT_LASTNAME,FORMAT(STUDENT_ENDDATE,'MM/dd/yyyy') AS DATE_FORMATFROM HARVARD_UNIVERSITY;

In the above query, the SELECT statement is used to retrieve all records of the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table.

The FORMAT function is used on the STUDENT_ENDDATE column to format the date in the form of MM/dd/yyyy HH:mm: ss” from the HARVARD_UNIVERSITY table.

We have used the ALIAS clause with the AS keyword on the FORMAT function of a column and given the name DATE_FORMAT for the output columm_

We hope that you have understood the subtopic “SQL Server Date Format MM/dd/yyyy hh:mm: ss” from the above query. For a better illustration, we have used an example and explained it in deepness.

Read: Drop stored procedure SQL Server

SQL Server Date Format mm/dd/yyyy Convert

In this section, we will learn and understand how to use the SQL Server CONVERT() function on the DATE FORMAT column as mm/dd/yyyy from the table by the query. And which will be explained with the help of an illustrated example.

The SQL Server CONVERT function is used to change the format of a date and if we have specified the requested date of a string and specify the format of the number corresponding to the number needed. Here is a syntax of the SQL Server CONVERT function for date format as mm/dd/yyyy from the table by the following query:

SYNTAX:

SELECT expression_1, expression_2, expression_n, CONVERT(VARCHAR, COLUMN_NAME, FORMAT_NUMBER)FROM YOUR_TABLE_NAME;

Let’s have a look at the SQL Server CONVERT function which will help to date format as mm/dd/yyyy from a table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;SELECT STUDENT_FIRSTNAME,STUDENT_LASTNAME,CONVERT(VARCHAR,STUDENT_ADMITDATE,101) CONVERT_DATEFORMATFROM HARVARD_UNIVERSITY;

In this preceding query, the SELECT statement will retrieve all records of the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERISTY table.

The CONVERT function is used with the VARCHAR data type and STUDENT_ADMITDATE column and data format number as 101 which will help to give date format as mm/dd/yyyy from the HARVARD_UNIVERSITY table.

In the end, we have not used the ALIAS clause with AS keyword and given the output column_name as CONVERT_DATEFORMAT. If we don’t use the ALIAS clause then also query will be executed properly without any error.

We hope that you have understood the subtopic “SQL Server Date Format mm/dd/yyyy Convert” from the table by the query. For a better explanation, we have used an example and explained it in deepness.

Read: SQL Server Convert String to Date

SQL Server 2008 Format Date mm/dd/yyyy

In this section, we will learn and understand how to use the SQL Server 2008 CAST, MONTH, and SYSDATETIME functions used to format date as mm/dd/yyyy from the table by the query. And which will be explained with the help of an illustrated example.

The SQL Server CAST function is used to convert a value of any type into a specified datatype. The MONTH function is used to extract the month portion value from the column_name or expression by the query. And the SYSDATETIME function is used to provide the current system date.

The DAY function is used to provide the day portion value from the column_name or expression by the query. And the YEAR function works the same way but it works and provides the year portion value from the expression by the query.

Here is an illustrated example of the SQL Server 2008 CAST, MONTH, and SYSDATETIME function that will be used to provide a date in the format as mm/dd/yyyy by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;SELECT STUDENT_ID,STUDENT_FIRSTNAME,STUDENT_LASTNAME,CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' +CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/D/YYYY]FROM HARVARD_UNIVERSITY;

• As we see in the above query, we have used the SELECT statement to retrieve all records of the STUDENT_ID, STUDENT_FIRSTNAME, and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table.
• The CAST function works with sub-function like MONTH, DAY, YEAR, and SYSDATETIME functions.
• The MONTH function will extract the month portion value from today’s system’s date but the CAST function will convert into varchar data type as MM value.
• The same method of the DAY and YEAR function will work which will provide the day and year portion value from today’s system date.
• To join all month, day, and year values with each other, we have used the ADD operator and backslash sign ( / ).
• In the end, we have used the ALIAS clause with the AS keyword and shorter the function_name, and given the name as M/D/YYYY for the output column_name.

We hope that you have understood the subtopic “SQL Server 2008 Format Date mm/dd/yyyy” from the table by the query. For a better explanation, we have used an example and explained it in depth.

Read: SQL Server Convert Datetime to date

SQL Server Date Format mm/dd/yyyy hh:mm:ss am

In this section, we will learn and understand how to use the SQL Server FORMAT function to format the date as mm/dd/yyyy hh:mm: ss am from the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;SELECT STUDENT_FIRSTNAME,STUDENT_LASTNAME,FORMAT(STUDENT_ADMITDATE,'MM/dd/yyyy HH:mm:ss tt') DATEFORMAT_AS_AMFROM HARVARD_UNIVERSITY;

• In this preceding query, the SELECT statement is used to retrieve all records of the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table.
• The FORMAT function is used on the STUDENT_ADMITDATE column to give the date format as MM/dd/yyyy HH: mm: ss tt from the HARVARD_UNIVERSITY table.
• When we will provide the tt as a value then the system will decide and show it as AM or PM. And please don’t forget the value as HH or hh otherwise it will format the hour value as 12 hours format.
• To shorter the function_name, we have not used the ALIAS clause with the AS keyword and given the temporary output column name as DATEFORMAT_AS_AM for the result set.

We hope that you have understood how to use the SQL Server FORMAT function to format the date as “mm/dd/yyyy hh:mm: ss am” from the table by the above query. For better understanding, we have used a demonstrated example and explained it in depth.

Read: Alter view in SQL Server

SQL Server Default Date Format dd/mm/yyyy

In this SQL Server section, we will learn and understand how to use SQL Server FORMAT and CURRENT_TIMESTAMP functions on the table by query. And which will be explained with the help of an illustrated example.

The SQL Server CURRENT_TIMESTAMP is used to provide the current date by the query. Here is an illustrated example of the SQL Server FORMAT and CURRENT_TIMESTAMP function which will help to provide the default date format dd/mm/yyyy From the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;SELECT STUDENT_FIRSTNAME,STUDENT_LASTNAME,FORMAT(CURRENT_TIMESTAMP,'dd/MM/yyyy ') AS DATE_FORMATFROM HARVARD_UNIVERSITY;

• In this preceding query, the SELECT statement is used to retrieve all records of the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table.
• The FORMAT function is used with the CURRENT_TIMESTAMP function to provide the date format as dd/MM/yyyy from the HARVARD_UNIVERSITY table.
• The CURRENT_TIMESTAMP function by the query will provide the current date as of 06/23/2022.
• To shorten the function_name, we used the ALIAS clause with the AS keyword and gave the name DATE_FORMAT for the output column name.

We hope that you have understood the subtopic “SQL Server Default Date Format dd/mm/yyyy” from the table by the query. For a better description, we have used an example and explained it with the deepness.

Read: How to call a view in SQL Server

SQL Server Select Date Format dd/mm/yyyy hh:mm

In this section, we will learn and understand how to use the FORMAT function to select the date format as dd/mm/yyyy hh: mm of the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;SELECT STUDENT_FIRSTNAME,STUDENT_LASTNAME,FORMAT(STUDENT_ENDDATE,'MM/dd/yyyy HH:mm') DATE_FORMATFROM HARVARD_UNIVERSITY;

• In this query, we have used the SELECT statement to retrieve all records of the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table.
• The FORMAT function is used on the STUDENT_ENDDATE column which will date format in the form of MM/dd/yyyy HH: mm from the HARVARD_UNIVERSITY table.
• In the end, we have used the ALIAS clause with the AS keyword which will help to keep the temporary name of the FORMAT function as DATE_FORMAT in that query.

We hope that you have understood how to use the SQL Server FORMAT function which will select the date format as MM/dd/yyyy HH: mm on the table by the query. For better understanding, we have used an illustrated example and explained it in deepness.

Read: Indexed views in SQL Server

SQL Server Regular Expression for Date Format mm/dd/yyyy

In this SQL Server tutorial, we will learn and understand how to use the SQL Server FORMAT function with the LIKE condition which will help to format the date as mm/dd/yyyy from the table by the query. And which will be explained with the help of an illustrated example.

In SQL Server, the REGULAR EXPRESSIONS is used to define specific patterns in Transact-SQL which works in a LIKE operator and filters the result based on the specific condition.

There are multiple types of regular expressions in the SQL Server which are given below:

• Alphabetic Regex
• Case Sensitive Regex
• Regex to Exclude Characters
• Numeric Regex
• Special Characters Regex

Here is an illustrated example of the SQL Server FORMAT function with the LIKE condition which will help to format the date as mm/dd/yyyy by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;SELECT STUDENT_FIRSTNAME,STUDENT_LASTNAME,FORMAT(STUDENT_ADMITDATE,'MM/dd/yyyy') AS [MM/DD/YYYY]FROM HARVARD_UNIVERSITYWHERE STUDENT_FIRSTNAME LIKE '[a]%';

• In this preceding query, the SELECT statement is used to retrieve all records of the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table.
• The FORMAT function is used on the STUDENT_ADMITDATE column to give the date format as MM/dd/yyyy from the HARVARD_UNIVERSITY table.
• In the WHERE condition, the STUDENT_FIRSTNAME column is used with the LIKE operator to find the alphabet ‘a’ at the end of records from the HARVARD_UNIVERSITY table.
• In the end, we have used the ALIAS clause to shorten the function name and given the MM/DD/YYYY for the output column_name.
• The SELECT statement will be able to retrieve all records of the HARVARD_UNIVERSITY table if the WHERE condition gets TRUE.
• But if the WHERE turns out to be FALSE then the SELECT statement will retrieve empty records for the output and the query will be executed properly without any error.

We hope we have understood the subtopic “SQL Server Regular Expression Date Format mm/dd/yyyy” by the query. For a better reason, we have used an example and explained it in profundity.

Read: How to use union in view SQL Server

SQL Server Date Format dd/mm/yyyy to yyyy-mm-dd

In this section, we will learn and understand how to use the SQL Server CONVERT function to convert the date format dd/mm/yyyy to yyyy-mm-dd from the table by the query. And we have used an example and explained it in detail.

The SQL Server CONVERT function is used to convert a value of any type into a special datatype. Here is an illustrated example of the SQL Server CONVERT function to convert the date format dd/mm/yyyy to yyyy-mm-dd from the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;SELECT STUDENT_FIRSTNAME,STUDENT_LASTNAME, CONVERT(char(10), STUDENT_ADMITDATE,126) AS [YYYY-MM-DD]FROM HARVARD_UNIVERSITY;

In this preceding query, the SELECT statement is used to retrieve all records of the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table.

The CONVERT function uses the CHAR data type for the STUDENT_ADMITDATE column with the date_format as 126. So that it can change the DATE FORMAT of the STUDENT_ADMITDATE column from dd/mm/yyyy to yyyy-mm-dd in the HARVARD_UNIVERSITY table.

In the end, we have used the ALIAS clause with the AS keyword and given the name YYYY-MM-DD for the output column_name. Here is one thing to remember while not giving the output name to function or any clause then it will keep (NO COLUMN NAME) as the new output column_name.

So just try to keep the temporary output name by the ALIAS clause so that it might become easier to understand for yourself.

We hope that you have understood the subtopic “SQL Server Date Format dd/mm/yyyy to yyyy-mm-dd” by the above query. For a better explanation, we have used an example and explained it in depth.

Also, take a look at some more SQL Server tutorials.

• Create a table from view in SQL Server
• How to view table in SQL Server
• Comparison Operators in SQL Server
• SQL Server logical operators and example

So, in this tutorial, we have learned how to use the SQL Server Date Format dd/mm/yyyy functionand we also covered the following set of topics:

• SQL Server Date Format mm/dd/yyyy
• SQL Server Date Format mm/dd/yyyy hh:mm:ss
• SQL Server Date Format mm/dd/yyyy
• SQL Server Date Format mm/dd/yyyy Convert
• SQL Server 2008 Format Date mm/dd/yyyy
• SQL Server Date Format mm/dd/yyyy hh:mm:ss am
• SQL Server Default Date Format dd/mm/yyyy
• SQL Server Select Date Format dd/mm/yyyy hh:mm
• SQL Server Regular Expression for Date Format mm/dd/yyyy
• SQL Server Date Format dd/mm/yyyy to yyyy-mm-dd