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!
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
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.
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
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
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
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
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. For a more comprehensive understanding of the CAST function and its applications in converting Varchar to Date in SQL Server, consider exploring our detailed guide.
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:
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:
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
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
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
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!
