Converting DateTime to YYYY-MM-DD Format in SQL Server

An SQL Server database can store a variety of data types, such as numbers, text strings, Boolean values, dates, etc. However, storing and handling such data have their specificities. The current article will focus on storing dates in an SQL Server database table and converting different types of dates into the SQL Server format YYYY-MM-DD and vice versa.

Let’s start!

CodingSight - Converting DateTime to YYYY-MM-DD Format in SQL Server

YYYY-MM-DD – the Date Format in SQL Server

The SQL Server YYYY-MM-DD data format suggests that the year is marked by four digits e.g., 2021. The month is specified next in 2 digits ranging from 1-12 – e.g., June would be 06. Finally, the day of the month is presented in 2 digits, such as 20. Thus, the date June 06, 2021, will be stored as 2021-06-21. It is the YYYY-MM-DD format for dates in SQL Server databases.

In SQL Server, you can use either Date or DateTime data type to store dates. The difference between the Date and DateTime data types lies in the level of detail in which both the data types store the date information.

  • The DateTime data type stores the date together with the time information in hours, minutes, and seconds.
  • The Date data type only allows you to store the date information without the time information.

The following example demonstrates the difference between the Date and DateTime data types.

First, we are creating a dummy MyDatabase database and one table (Patient) in it.

The Patient table has 5 columns: Id, Name, Gender, DOB (Date of Birth), and DOD (Date of Death).  The type of the DOB column is Date, whereas the DOD column has the DateTime data type.

CREATE DATABASE MyDatabase

USE MyDatabase
CREATE TABLE Patient

(
Id INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR (50) NOT NULL,
Gender VARCHAR (50),
DOB Date,
DOD DateTime
)

The following script inserts some dummy records into the Patient table:

INSERT INTO Patient
VALUES ('Jack', 'Male', '1960-12-15', '2017-06-30 16:30:35'),
('Sara', 'Female', '1962-01-20', '2015-02-22 10:35:55'),
('Elisa', 'Female', '1959-03-25', '2020-03-16 22:24:39'),
('Nik', 'Male', '1938-12-15', '2011-06-21 09:45:55'),
('Jos', 'Male', '1940-09-12', '2015-03-25 11:55:20')

Notice the difference between the values inserted into the DOB column and the DOD column. The DOB column has values in YYYY-MM-DD format, but for the DOD column, you pass the HH:MM: SS (hours: minutes: seconds) information.

Let’s now see how our Patient table looks. Execute the following SQL script to select all records from the Patient table:

SELECT * FROM Patient
YYYY-MM-DD - the Date Format in SQL Server

How To Convert DateTime to Date Format YYYY-MM-DD in SQL Server

Often, we need only the date part from the DateTime column. Since the date values are stored in SQL Server in YYYY-MM-DD format by default, extracting the date part from the DateTime data type returns the date in this format.

Let’s have a look at an example. The DOD column in the Patient table stores values in the DateTime format. Let’s see how to extract the Date part from the column:

SELECT CAST(DOD AS DATE) AS DOD_Date
FROM Patient

As you can see from the script above, to convert the DateTime type column to Date, you can use the CAST function. You need to pass the column name followed by the AS statement and the DATE type to the CAST function.

Here is the result of your script: values from the DOD column are selected and converted into YYYY-MM-DD format.

Converting DateTime to SQL Server Date Format YYYY-MM-DD

Let’s select both the original DOD column values and converted DOD values to demonstrate the difference between them:

SELECT DOD as DOD, CAST(DOD AS DATE) AS DOD_Date
FROM Patient
original DOD column values and converted DOD values

Finally, if you want to select all columns in your table and cast only a single column (e.g., convert the DOD column to Date type), you can run the following script:

SELECT t.*, CAST(DOD AS DATE) AS DOD_Date
from Patient t
all table columns along with the DOD_Date column containing the Date part from the DOD column

In the output, you can see all table columns along with the DOD_Date column containing the Date part from the DOD column.

How to Convert String to SQL Server Date Format YYYY-MM-DD

Dates are often stored in string formats in the SQL Server table columns. You can convert the string values to the SQL Server date format YYYY-MM-DD.

Let’s create another column in our Patient table. The name of the column is Arriv_Date (dummy column that shows patients’ arrival dates), and the column type is VARCHAR. Execute the following script to create this column:

ALTER TABLE Patient
ADD Arriv_Date varchar(50);

You receive the new Arriv_Date column added to the Patient column. At the moment, the Arriv_Date column contains NULL values.

SELECT * FROM Patient
Converting Strings to SQL Server Date Format YYYY-MM-DD

Let’s now add some records into the Arriv_Date column. The following script inserts strings in various date formats to this column.

UPDATE Patient SET Arriv_Date = '10-Nov-2012' WHERE Id = 6
UPDATE Patient SET Arriv_Date = 'March 31, 2015' WHERE ID = 7
UPDATE Patient SET Arriv_Date = '12/02/2020' WHERE ID = 8
UPDATE Patient SET Arriv_Date = '10 October 2020' WHERE ID = 9
UPDATE Patient SET Arriv_Date = '15/10/21' WHERE ID = 10

Now, let’s see how the data in the Arriv_Date column looks. Execute the script below:

SELECT * FROM Patient
how the data in the Arriv_Date column looks

To convert all strings in the Arriv_Date column to the SQL Server date format YYYY-MM-DD, you can again use the CAST function. You need to specify the column name followed by the AS statement and the DATE type.

The following script selects the original Arriv_Date column and the values in it that are converted into date format.

Select Arriv_Date, CAST(Arriv_Date as DATE) as ARRIVAL_DATE
FROM Patient

The output shows values in different date formats. The string type column Arriv_Date gets converted to the YYYY-MM-DD format in the ARRIVAL_DATE column:

The string type column Arriv_Date gets converted to the YYYY-MM-DD format in the ARRIVAL_DATE column

How to Convert SQL Server Date Format YYYY-MM-DD Into Other Formats

Just as you can convert other formats to YYYY-MM-DD, you can do the reverse. To convert the YYYY-MM-DD date format to strings with different date formats, you can use the CONVERT function.

The CONVERT function accepts three parameters: the target type which is VARCHAR(50) in the following script, the original date column, and the code. The code defines how the converted date will look like. Here the code value of 105 converts the date into DD-MM-YYYY format.

Run the script to convert the date from the YYYY-MM-DD format to DD-MM-YYYY:

SELECT DOB, CONVERT(varchar(50), DOB ,105) as DOB_CONV
From Patient

Here is the output:

Converting SQL Server Date Format YYYY-MM-DD Into Other Formats

Let’s have another example. The code value of 106 for the converted date converts the date from YYYY-MM-DD format to DD MON YYYY format. For instance, in the first column, the date 1960-12-15 is converted to 12 Dec 1960.

SELECT DOB, CONVERT(varchar(50), DOB ,106) as DOB_CONV
From Patient
The code value of 106 for the converted date converts the date from YYYY-MM-DD format to DD MON YYYY format

Similarly, the code value of 6 converts a date to DD MON YY format:

SELECT DOB, CONVERT(varchar(50), DOB ,6) as DOB_CONV
From Patient
the code value of 6 converts a date to DD MON YY format

And finally, the code value of 11 converts a date to YYYY/MM/DD format:

SELECT DOB, CONVERT(varchar(50), DOB ,111) as DOB_CONV
From Patient
the code value of 11 converts a date to YYYY/MM/DD format

To see further details of all the various date formats and corresponding codes, refer to the official SQL Server documentation.

Conclusion

This way, we’ve clarified both the essence of the SQL server date format YYYY-MM-DD and the specific cases of converting the dates into this format and vice versa.

We learned to use the CAST function to convert different data types including DateTime and strings to YYYY-MM-DD format and the CONVERT function to turn the YYYY-MM-DD dates to other formats.

Besides, the modern tools for SQL Server specialists make the jobs of editing the dates much simpler. For instance, the multi-featured dbForge Studio for SQL Server includes, among the rest of options, a handy Visual Data Editor that you can use when working with dates in SQL Server.

Hope this information was useful for you. Stay tuned!  

Ben Richardson
Latest posts by Ben Richardson (see all)

Ben Richardson

Ben Richardson runs Acuity Training. Acuity is an IT training business offering classroom courses in London and Guildford. It is a leading provider of SQL training the UK and offers a full range of SQL training from introductory training to advanced administration courses.

Leave a Reply

Your email address will not be published. Required fields are marked *