SQL Server Insert Date

by

In this comprehensive article, I’ll cover everything you need to know about inserting dates into SQL Server, from the basics to advanced techniques that I have learned working as a senior SQL resource for a long time.

SQL Server Insert Date

Before going deep into insertion approaches, it’s essential to understand the different date and time data types available in SQL Server:

Data TypeStorage SizeRangePrecision
DATE3 bytes0001-01-01 to 9999-12-311 day
DATETIME8 bytes1753-01-01 to 9999-12-313.33 milliseconds
DATETIME26-8 bytes0001-01-01 to 9999-12-31100 nanoseconds
SMALLDATETIME4 bytes1900-01-01 to 2079-06-061 minute
TIME3-5 bytes00:00:00.0000000 to 23:59:59.9999999100 nanoseconds
DATETIMEOFFSET8-10 bytes0001-01-01 to 9999-12-31 (with time zone)100 nanoseconds

Choosing the right data type is the first step in ensuring efficient date handling. It’s recommended to use DATETIME2 as it offers better range, precision, and performance than the older DATETIME type.

Different Approaches for Inserting Dates in SQL Server

Approach 1: Using ISO Format (YYYY-MM-DD)

The most reliable and language-independent way to insert dates is using the ISO format (YYYY-MM-DD):

INSERT INTO StudentDetail (Studentid, studentname, country, Age, DOB)
VALUES (1001, 'Mary Johnson','USA',23, '2022-11-15');

After executing the above query, I got the expected output in the screenshot below.

SQL Server Insert Date

This method works regardless of your SQL Server’s language or regional settings, making it the most portable option.

Approach 2: Using the CONVERT or CAST Functions

When your date is in a different format, you can use CONVERT or CAST to ensure proper insertion:

Let us first use the following query for the CONVERT function.

-- Using CONVERT
INSERT INTO StudentDetail (Studentid, studentname, country, Age, DOB)
VALUES (1001, 'Mary Johnson','USA',23, CONVERT(DATETIME, '11/15/2021', 101));

I got the expected output after executing the above query as shown in the screenshot below.

sql server insert date into table

Now, let us use the following query for the CAST function.

INSERT INTO StudentDetail (Studentid, studentname, country, Age, DOB)
VALUES (1001, 'Steve Smith','USA',23, CAST('2021-11-15' AS DATETIME));

I got the expected output after executing the above query as shown in the screenshot below.

sql server insert date format

Approach 3: Using SQL Server’s Built-in Date Functions

We can use the below query to insert only the current date (no time component).

INSERT INTO StudentDetail (Studentid, studentname, country, Age, DOB)
VALUES (1001, 'John Smith','USA',23, CAST(GETDATE() AS DATE));

After executing the above query, I got the expected output as shown in the screenshot below.

how to insert a date in sql server

Approach 4: Specifying Date and Time Together

When you need to insert a datetime value with both date and time components in the ISO format, use the following query.

INSERT INTO StudentDetail (Studentid, studentname, country, Age, DOB)
VALUES (1003, 'Johnson','UK',21, '2022-11-15 14:30:00');

After executing the above query, I got the successful output as shown in the screenshot below.

how to insert date value in sql server

You can execute the following query if you wish to insert the date value with milliseconds precision.

INSERT INTO StudentDetail (Studentid, studentname, country, Age, DOB)
VALUES (1003, 'Johnson','UK',21, '2023-11-15 02:15:30.123');

After executing the above query, I got the successful output as shown in the screenshot below.

how to insert date in sql server

Method 6: Inserting Dates with Time Zone Information

For applications that operate across multiple time zones, using DATETIMEOFFSET ensures time zone consistency:

INSERT INTO InternationalOrders (OrderID, CustomerID, OrderDateTime)
VALUES (9001, 3001, '2023-11-15 14:30:00 -05:00');
how to insert default date in sql server

Method 7: Dealing with Different Date Formats

We can use the below query, converting from MM/DD/YYYY (US format).

-- Converting from MM/DD/YYYY (US format)
INSERT INTO StudentDetail (Studentid, studentname, country, Age, DOB)
VALUES (1004, 'Johnson','UK',21, CONVERT(DATE, '12/25/1985', 101));

After executing the above query, I got the expected output as shown in the screenshot below.

sql server insert date format dd mm yyyy

You use the below query converting from DD/MM/YYYY (European format).

-- Converting from DD/MM/YYYY (European format)
INSERT INTO StudentDetail (Studentid, studentname, country, Age, DOB)
VALUES (1004, 'Johnson','UK',21, CONVERT(DATE, '25/12/1990', 103));

After executing the above query, I got the expected output as shown in the screenshot below.

sql server insert date only

Best Practices

Below are the best practices for handling date insertion:

  1. Choose the appropriate date data type for your needs:
    • Use DATE when you only need the date component
    • Use DATETIME2 for most modern applications requiring both date and time
    • Use DATETIMEOFFSET when time zone information is important
  2. Use ISO format (YYYY-MM-DD) whenever possible for maximum compatibility and clarity.
  3. Consider default constraints for automatically setting creation dates;
  4. Validate dates before insertion to ensure they fall within expected ranges.

Conclusion

Knowing how to insert dates in SQL Server is an essential skill for any database developer. Following the approaches explained in this article, you’ll insert the date in SQL Server with little effort.

You may also like the following articles.