Skip to main content

How to Use the CONVERT() Function for DateTime Conversion in MS SQL Server?

Image
By mvryo


Photo by Jon Tyson on Unsplash

In SQL Server, date and time management is essential for various applications, ranging from logging transactions to scheduling tasks. The CONVERT() function in MS SQL Server is a powerful tool for converting data types, especially when it comes to date and time formats. In this guide we will explore the CONVERT() function for DateTime conversion, guiding you through its usage, examples, and practical applications.

Understanding the Basics

The CONVERT() function in SQL Server is primarily used to convert an expression from one data type to another. The syntax for the CONVERT() function is as follows:

CONVERT(data_type [ (length) ], expression [, style])

  • data_type — the target data type you want to convert to.
  • length — an optional parameter that defines the length of the target data type.
  • expression —the value or column you want to convert.
  • style — an optional parameter that specifies the format of the output. Different styles can yield various date formats.

For DateTime conversions, the style parameter is particularly useful, as it allows you to display dates in different formats, such as MM/DD/YYYY, DD-MM-YYYY, and more.

Setting Up the Environment

To follow along with the examples in this article, you’ll need:

  1. SQL Server: Ensure you have access to a SQL Server instance. You can use SQL Server Management Studio (SSMS) or any SQL client of your choice.
  2. Sample Database: If you don’t have a sample database, you can create one.

Here’s how to create a simple table for demonstration:

CREATE TABLE SampleDates (
    Id INT PRIMARY KEY,
    OriginalDate DATETIME
);

INSERT INTO SampleDates (Id, OriginalDate) VALUES
(1, '2023-01-01'),
(2, '2023-02-15'),
(3, '2023-03-20');

Working with the CONVERT() Function

Now that we have the environment set up, let’s dive into the CONVERT() function for DateTime conversions. Here are some common uses:

1. Basic DateTime Conversion

To convert a DateTime to a different format, use the CONVERT() function with the desired style:

SELECT
    OriginalDate,
    CONVERT(DATETIME, OriginalDate, 101) AS FormattedDate
FROM
    SampleDates;

In this example, the 101 style converts the date to the MM/DD/YYYY format.

2. Converting to Different Formats

You can convert DateTime to various formats by changing the style parameter. Here are some standards you can use:

Default standard

-- Format: mon dd yyyy hh:miAM/PM
-- Style: 
-- 0 - without Century
-- 100 - with Century

SELECT 
  CONVERT(DATETIME, 'Jul 15 2020 00:00AM', 0) AS WithoutCentury,
  CONVERT(DATETIME, 'Jul 15 2020 00:00AM', 100) AS WithCentury 
FROM 
  SampleDates

-- Format: mon dd yyyy hh:mi:ss:mmmAM (or PM)
-- Style: 
-- 9 - without Century
-- 109 - with Century

SELECT 
  CONVERT(DATETIME, 'Jul 15 2020 00:00:00:000AM', 9) AS WithoutCentury,
  CONVERT(DATETIME, 'Jul 15 2020 00:00:00:000AM', 109) AS WithCentury 
FROM 
  SampleDates

US/USA standard

-- Format: mm/dd/yyyy
-- Style: 
-- 1 - without Century
-- 101 - with Century

SELECT 
  CONVERT(DATETIME, '07/15/2020', 1) AS WithoutCentury,
  CONVERT(DATETIME, '07/15/2020', 101) AS WithCentury 
FROM 
  SampleDates

-- Format: mm-dd-yyyy
-- Style: 
-- 10 - without Century
-- 110 - with Century

SELECT 
  CONVERT(DATETIME, '07-15-2020', 10) AS WithoutCentury,
  CONVERT(DATETIME, '07-15-2020', 110) AS WithCentury 
FROM 
  SampleDates

ANSI Standard

-- Format: yyyy.mm.dd
-- Style: 
-- 2 - without Century
-- 102 - with Century

SELECT 
  CONVERT(DATETIME, '2020.07.15', 2) AS WithoutCentury,
  CONVERT(DATETIME, '2020.07.15', 102) AS WithCentury 
FROM 
  SampleDates

British/French Standard

-- Format: dd/mm/yyyy
-- Style: 
-- 3 - without Century
-- 103 - with Century

SELECT 
  CONVERT(DATETIME, '15/07/2020', 3) AS WithoutCentury,
  CONVERT(DATETIME, '15/07/2020', 103) AS WithCentury 
FROM 
  SampleDates

German Standard

-- Format: dd.mm.yyyy
-- Style: 
-- 4 - without Century
-- 104 - with Century

SELECT 
  CONVERT(DATETIME, '15.07.2020', 4) AS WithoutCentury,
  CONVERT(DATETIME, '15.07.2020', 104) AS WithCentury 
FROM 
  SampleDates

Italian Standard

-- Format: dd-mm-yyyy
-- Style: 
-- 5 - without Century
-- 105 - with Century

SELECT 
  CONVERT(DATETIME, '15-07-2020', 5) AS WithoutCentury,
  CONVERT(DATETIME, '15-07-2020', 105) AS WithCentury 
FROM 
  SampleDates

Japan Standard

-- Format: yyyy/mm/dd
-- Style: 
-- 11 - without Century
-- 111 - with Century

SELECT 
  CONVERT(DATETIME, '2020/07/15', 11) AS WithoutCentury,
  CONVERT(DATETIME, '2020/07/15', 111) AS WithCentury 
FROM 
  SampleDates

ISO Standard

-- Format: yyyymmdd
-- Style: 
-- 12 - without Century
-- 112 - with Century

SELECT 
  CONVERT(DATETIME, '20200715', 12) AS WithoutCentury,
  CONVERT(DATETIME, '20200715', 112) AS WithCentury 
FROM 
  SampleDates

Europe (24-hour clock) Standard

-- Format: dd mon yyyy hh:mi:ss:mmm
-- Style: 
-- 13 - without Century
-- 113 - with Century

SELECT 
  CONVERT(DATETIME, '15 Jul 2020 00:00:00:000', 13) AS WithoutCentury,
  CONVERT(DATETIME, '15 Jul 2020 00:00:00:000', 113) AS WithCentury 
FROM 
  SampleDates

24-hour Clock Standard

-- Format: hh:mi:ss:mmm
-- Style: 
-- 14 - without Century
-- 114 - with Century

SELECT 
  CONVERT(DATETIME, '00:00:00:000', 14) AS WithoutCentury,
  CONVERT(DATETIME, '00:00:00:000', 114) AS WithCentury 
FROM 
  SampleDates

ODBC Canonical (24-hour clock) Standard

-- Format: yyyy-mm-dd hh:mi:ss
-- Style: 
-- 20 - without Century
-- 120 - with Century

SELECT 
  CONVERT(DATETIME, '2020-07-15 00:00:00', 20) AS WithoutCentury,
  CONVERT(DATETIME, '2020-07-15 00:00:00', 120) AS WithCentury 
FROM 
  SampleDates

-- Format: yyyy-mm-dd hh:mi:ss.mmm
-- Style: 
-- 21 - without Century
-- 121 - with Century

SELECT 
  CONVERT(DATETIME, '2020-07-15 00:00:00.000', 21) AS WithoutCentury,
  CONVERT(DATETIME, '2020-07-15 00:00:00.000', 121) AS WithCentury 
FROM 
  SampleDates

ISO8601 Standard

-- Format: yyyy-mm-ddThh:mi:ss.mmm
-- Style: 
-- 126 - with Century

SELECT 
  CONVERT(DATETIME, '2020-07-15T00:00:00.000', 126) AS WithCentury 
FROM 
  SampleDates

-- Format: yyyy-mm-ddThh:mi:ss.mmmZ
-- Style: 
-- 127 - with Century

SELECT 
  CONVERT(DATETIME, '2020-07-15T00:00:00.000Z', 127) AS WithCentury 
FROM 
  SampleDates

Hijiri Standard

-- Format: dd mon yyyy hh:mi:ss:mmmAM
-- Style: 
-- 130 - with Century

SELECT 
  CONVERT(DATETIME, '15 Jul 2020 00:00:00.000AM', 130) AS WithCentury 
FROM 
  SampleDates

-- Format: dd/mm/yy hh:mi:ss:mmmAM
-- Style: 
-- 131 - with Century

SELECT 
  CONVERT(DATETIME, '15/07/20 00:00:00.000AM', 131) AS WithCentury 
FROM 
  SampleDates

Other Formats

-- Format: dd mon yyyy
-- Style: 
-- 6 - without Century
-- 106 - with Century

SELECT 
  CONVERT(DATETIME, '15 Jul 2020', 6) AS WithoutCentury,
  CONVERT(DATETIME, '15 Jul 2020', 106) AS WithCentury 
FROM 
  SampleDates

-- Format: Mon dd, yyyy
-- Style: 
-- 7 - without Century
-- 107 - with Century

SELECT 
  CONVERT(DATETIME, 'Jul 15, 2020', 7) AS WithoutCentury,
  CONVERT(DATETIME, 'Jul 15, 2020', 107) AS WithCentury 
FROM 
  SampleDates

-- Format: hh:mm:ss
-- Style: 
-- 8 - without Century
-- 108 - with Century

SELECT 
  CONVERT(DATETIME, '00:00:00', 8) AS WithoutCentury,
  CONVERT(DATETIME, '00:00:00', 108) AS WithCentury 
FROM 
  SampleDates

Practical Examples

Let’s explore some practical examples to solidify your understanding of using the CONVERT() function:

Example 1: Converting a Date to String

To convert a DateTime to a string with a specific format:

SELECT
    CONVERT(VARCHAR(50), OriginalDate, 120) AS FormattedDate
FROM
    SampleDates;

Example 2: Using CONVERT() in a WHERE Clause

You can also use the CONVERT() function in a WHERE clause to filter data based on formatted dates:

SELECT *
FROM SampleDates
WHERE CONVERT(VARCHAR(10), OriginalDate, 101) = '01/01/2023';

Example 3: Inserting Formatted Dates

You can insert dates in a specific format using CONVERT():

DECLARE @dateString VARCHAR(10) = '12/25/2023';

INSERT INTO SampleDates (Id, OriginalDate)
VALUES (4, CONVERT(DATETIME, @dateString, 101));

Common Use Cases

The CONVERT() function is widely used in various scenarios:

  • Reporting: Format dates for reports to meet specific formatting requirements.
  • Data Import/Export: Prepare data for external applications that require specific date formats.
  • Data Validation: Ensure date strings conform to the expected formats before processing.

Conclusion

The CONVERT() function in MS SQL Server is a versatile tool for DateTime conversion, allowing you to manipulate and format dates as needed. By understanding its syntax and various styles, you can effectively manage date formats for your applications.

Now that you’re equipped with the knowledge of using the CONVERT() function for DateTime conversion, try it out in your own SQL Server environment! Experiment with different styles and formats, and share your experiences in the comments below. If you found this article helpful, consider sharing it with your fellow developers!

Don't forget to subscribe to my blog so you never miss out on my latest guides and content!







Labels:

Comments

Post a Comment

Popular posts from this blog

Understanding Number Systems: Decimal, Binary, and Hexadecimal

By mvryo
In everyday life, we use numbers all the time, whether for counting, telling time, or handling money. The number system we’re most familiar with is the   decimal system , but computers use other systems, such as   binary   and   hexadecimal . Let’s break down these number systems to understand how they work. What is a Number System? A number system is a way of representing numbers using a set of symbols and rules. The most common number systems are: Decimal (Base 10) Binary (Base 2) Hexadecimal (Base 16) Each system has a different “base” that tells us how many unique digits (symbols) are used to represent numbers. Decimal Number System (Base 10) This is the system we use daily. It has  10 digits , ranging from  0 to 9 . Example: The number  529  in decimal means: 5 × 1⁰² + 2 × 1⁰¹ + 9 × 1⁰⁰ =  500 + 20 + 9 = 529 Each position represents a power of 10, starting from the rightmost digit. Why Base 10? Decimal is base 10 because it has 10 digits...
Post a Comment
Read More »

How to Monetize Your API as an Individual Developer While Hosting on Your Own Server?

By mvryo
In the API economy, cloud services like AWS, Google Cloud, and Azure offer many conveniences, such as scaling and infrastructure management. However, some developers prefer more control and autonomy, opting to host their APIs on personal servers. Whether for cost efficiency, data privacy, or customization, hosting your own API comes with both advantages and challenges. But, even without cloud platforms, there are effective ways to monetize your API. This guide will explore how individual developers can successfully monetize their APIs while hosting them on their own servers. Why Host Your API on Your Own Server? Hosting your own API gives you full control over the infrastructure and potentially lower long-term costs. Here’s why some developers choose this approach: Cost Control : Instead of paying ongoing cloud fees, you may opt for a one-time or lower-cost hosting solution that fits your budget and resource needs. Data Ownership : You have full control over data, which is critical if ...
Post a Comment
Read More »

API Testing with Jest and Supertest: A Step-by-Step Guide

By mvryo
API testing is essential to ensure your endpoints behave as expected across all scenarios. In this guide, we’ll explore how to use Jest and Supertest to test a sample API with various response types, including success, authentication errors, and validation errors. By the end, you’ll understand how to apply these tools to check for different response structures and status codes. 0. Prerequisites: Setting Up Your Environment Before diving into API testing, it’s important to ensure that your development environment is properly set up. Here’s what you need to do: Step 1: Install Node.js and npm Node.js  is a JavaScript runtime that allows you to run JavaScript code on the server side. It comes with  npm  (Node Package Manager), which helps you install and manage packages. Installation Steps: Download and install Node.js from the  official website . To verify the installation, open your terminal and run: node -v npm -v This should display the installed versions of Node.js...
Post a Comment
Read More »

The Weight of Responsibility: A Developer’s Journey to Balance Passion and Reality

By mvryo
For the past several years, Eddie has been on a steady climb in his career as a developer, but recently, he found himself at a crossroads — caught between the weight of his responsibilities and the desire to pursue his true passions. His journey began with a three-month internship as a web developer, which led to nearly four years in an application developer role. After that, he spent almost a year as a systems associate, managing tasks across systems analysis, quality assurance, and business analysis. Eventually, he returned to full-time software development for another two years before transitioning into more complex roles. For over a year, he worked as a multi-role software developer and database administrator before stepping into his current position as a senior software developer, database administrator, and cloud administrator — occasionally handling security tasks as well. Now, with over 8 years of professional experience, he also leads a small team of developers, which has been...
Post a Comment
Read More »

Avoiding Confusion in API Design: The Importance of Clear Responses

By mvryo
In today’s fast-paced software development landscape, APIs play a crucial role in connecting services and enabling functionality. However, poor design choices can lead to confusion and inefficiency for both developers and users. One such choice is the omission of a response body for successful requests, a practice I recently encountered in an enterprise API designed for bill payments. The Case of the No-Response API The API in question serves two main endpoints: one for inquiring about account validity and another for confirming payment. When successful, the API returned a  200 OK  status but no response body. This design choice led to significant confusion during our integration process. Even the internal team who developed the said API struggled to justify this approach, revealing a lack of clarity around the rationale behind it. Pros of This Design Choice While the intention behind this design may have been to streamline responses, several potential benefits can be identifi...
Post a Comment
Read More »