SQL convert date

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

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:

CREATE TABLE [dbo].[delivers](

[productid] [tinyint] NOT NULL,

[date] [nvarchar](100) NULL,

CONSTRAINT [PK_delivers] PRIMARY KEY CLUSTERED

(

[productid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

INSERT [dbo].[delivers] ([productid], [date]) VALUES (1, N'02-03-2005')

INSERT [dbo].[delivers] ([productid], [date]) VALUES (2, N'03-05-2006')

INSERT [dbo].[delivers] ([productid], [date]) VALUES (3, N'04-05-2011')

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:

1 2	declare @vardate varchar(100)='03-04-2016' select CAST(@vardate AS datetime) AS dataconverted;

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.

1 2	declare @vardate varchar(100)='03-04-2016' select CONVERT(datetime, @vardate) as dataconverted

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:

1	select CONVERT(nvarchar(30),getdate(), 121) as isoformat

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:

1	select CONVERT(nvarchar(30),getdate(), 111) as Japanformat

For USA format:

1	select CONVERT(nvarchar(30),getdate(), 110) as USAformat

For ANSI format:

1	select CONVERT(nvarchar(30),getdate(), 102) as ANSIformat

For British format:

1	select CONVERT(nvarchar(30),getdate(), 103) as Britishformat

For German format:

1	select CONVERT(nvarchar(30),getdate(), 104) as Germanformat

For Italian format:

1	select CONVERT(nvarchar(30),getdate(), 105) as Italianformat

For European default format:

1	select CONVERT(nvarchar(30),getdate(), 113) as EuropeDefaultformat

For ODBC Canonical:

1	select CONVERT(nvarchar(30),getdate(), 120) as ODBCCanonicalformat

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

1	SELECT FORMAT( getdate(), 'dd/MM/yy')

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:

1 2	declare @vardate varchar(100)='11242016' select CONVERT(datetime, @vardate) as dataconverted

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:

declare @vardate varchar(100)='11242016'

set @vardate= SUBSTRING(@vardate, 1, 2)+'-'+ SUBSTRING(@vardate, 3, 2)+'-'+SUBSTRING(@vardate, 5, 4)

select CONVERT(date, @vardate) as dataconverted

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:

1	SELECT CONVERT(INT, GETDATE())

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:

declare @year int = year(getdate())

declare @month int = month(getdate())

declare @day int = day(getdate())

select @year as year,@month as month,@day as day

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.

Author
Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

PostgreSQL tutorial to create a user - November 12, 2023
PostgreSQL Tutorial for beginners - April 6, 2023
PSQL stored procedures overview and examples - February 14, 2023