Can you convert int to date in SQL?

Introduction

A common task for newbies is to learn how to do a SQL convert date and work to convert them date to other data types or covert other data types to Date.

Here in this article we will explain how to work and convert dates to different formats or vice versa.

Requirements

1. SQL Server installed. Starting in SQL Server 2008

Example

The first example will be simple, we have a varchar column with a date in a table, but we need to convert the varchar to date. We need to do a SQL convert date.

Here it is script to create the table with data:

We want to convert the column date from nvarchar(100) to a date.

To do it, we are going to try to modify the design of the table:

We will try to change the Data Type to smalldatetime:

You will receive the following error message:

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

To solve this error, in SSMS go to Tools > Options menu:

In Options, go to Designers >Table and Database Designers and uncheck the Prevent saving changes that require table re-creation:

This option will disable to option to prevent saving table recreation. When you modify the column data type, it requires table re-creation.

Now, you can save the design and your table will be converted to date and the SQL convert date is completed:

Conversion functions

T-SQL contains functions to convert data types. We will use CAST and CONVERT to do a SQL convert date.

Let’s start with CAST first:

How to convert from varchar, nvarchar, char, nchar to sql date using CAST

The following example, will show how to convert characters to a datetime date type using the CAST function:

The example declares a variable named vardate and then this variable that is a varchar is converted to datetime using the CAST function.

Note: For more information about the CAST function, refer to this link: CAST and CONVERT (Transact-SQL)

How to do a SQL convert date from varchar, nvarchar, char, nchar to date using CONVERT

CONVERT is a function that can do the same than CAST in the previous scenario.

The T-SQL code is doing the same than cast, but it is using the CONVERT function. The advantage of CONVERT is that you can easily change the format of the date using the style argument.

For example, if you want the date in the ISO format, you can use the following T-SQL sentence:

How to convert sql date into different formats in T-SQL

The following example shows how to convert the date format in different formats.

For Japananes format:

For USA format:

For ANSI format:

For British format:

For German format:

For Italian format:

For European default format:

For ODBC Canonical:

You always have the option to use the FORMAT function to get the date in the format that you want:

FORMAT is easier to handle dates and use the format of your preference, because you do not need to know the style. However, in my experience I see a lot of code using the CAST and CONVERT functions so, it is better to know them.

Note: For more information about the FORMAT function, refer to this link: FORMAT (Transact-SQL)

Problems related to SQL convert date operations

When you try to convert to date it is not always possible. The following example shows a common error:

The error message is the following:

Msg 242, Level 16, State 3, Line 22

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

You need separators for the date like a “/”, a “.” or a “-“.

The following example, modifies the string from 11242016 to 11-24-2016 and then converts to sql date:

We use substring to concatenate the “-” to use an acceptable date format and then we use the CONVERT function to convert the characters to sql date.

Date data types

In SQL Server, there are several types of date datatypes:

• Time returns the hours, minutes, seconds and nanoseconds (hh:mm:ss.nnnnnn)
• Date returns the year, months and days (yyyy-mm-dd)
• Datetime returns data with this format: YYYY-MM-DD hh:mm:ss[.nnn]
• Smalldatetime returns date with this format: YYYY-MM-DD hh:mm:ss
• Datetime2 is similar to Datetime, but it has more precision (YYYY-MM-DD hh:mm:ss[.nnnnnnn])
• Datetimeoffset it has the precision of datetime2, but it is used for time zones in UTC

SQL convert date to integer

If you use the CONVERT or CAST to convert a datetime to integer, it will return the number of days since 1900 until the date provided.

For example, the following T-SQL code will show the number of days from 1900 until today:

You can also convert to integer the year, months, days, etc. of a datetime value. The following code shows how to store in integer variables the day, month and year of a datetime value:

Common Questions about SQL convert date in SQL Server

Note: The following link contains FAQ about functions and dates in SQL Server: FAQ about Dates in SQL Server

Conclusions

In this article, we learned how to do a SQL convert date in SQL Server. We learned how to modify the data type in a table, how to use the CAST, CONVERT and FORMAT functions. We also learned about the different types of SQL data types.

How can I get date from integer in SQL?

Can we convert INT to string in SQL?

How do I convert INT to numeric in SQL?

How convert date to DD MMM YYYY in SQL?