{"id":46316,"date":"2019-04-01T13:00:45","date_gmt":"2019-04-01T13:00:45","guid":{"rendered":"https:\/\/www.sqlshack.com\/?p=46316"},"modified":"2019-11-28T11:33:52","modified_gmt":"2019-11-28T11:33:52","slug":"sql-convert-date","status":"publish","type":"post","link":"https:\/\/www.sqlshack.com\/sql-convert-date\/","title":{"rendered":"SQL convert date"},"content":{"rendered":"

Introduction<\/h2>\n

\n 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.\n<\/p>\n

<\/p>\n

\n Here in this article we will explain how to work and convert dates to different formats or vice versa.\n<\/p>\n

Requirements<\/h2>\n
    \n
  1. \n SQL Server installed. Starting in SQL Server 2008\n<\/li>\n<\/ol>\n

    Example<\/h2>\n

    \n 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.\n<\/p>\n

    \n Here it is script to create the table with data:\n<\/p>\n

    CREATE TABLE [dbo].[delivers](\r\n  [productid] [tinyint] NOT NULL,\r\n  [date] [nvarchar](100) NULL,\r\n CONSTRAINT [PK_delivers] PRIMARY KEY CLUSTERED \r\n(\r\n  [productid] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\n) ON [PRIMARY]\r\nGO\r\nINSERT [dbo].[delivers] ([productid], [date]) VALUES (1, N'02-03-2005')\r\nINSERT [dbo].[delivers] ([productid], [date]) VALUES (2, N'03-05-2006')\r\nINSERT [dbo].[delivers] ([productid], [date]) VALUES (3, N'04-05-2011')<\/pre>\n<\/p>\n
    \n  We want to convert the column date from nvarchar(100) to a date.\n<\/p>\n
    \n  To do it, we are going to try to modify the design of the table:\n<\/p>\n
    \n  \"SSMS
    \n  We will try to change the Data Type to smalldatetime:\n<\/p>\n
    \n  \"change
    \n  You will receive the following error message:\n<\/p>\n
    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\u2019t be re-created or enabled the option Prevent saving changes that require the table to be re-created.<\/strong>\n<\/p>\n
    \n  To solve this error, in SSMS go to Tools<\/strong> > Options<\/strong> menu:\n<\/p>\n
    \n  \"Change
    \n  In Options, go to Designers >Table and Database Designers<\/strong> and uncheck the Prevent saving changes that require table re-creation:\n  <\/p>\n
    \n  \"Uncheck
    \n  This option will disable to option to prevent saving table recreation. When you modify the column data type, it requires table re-creation.\n<\/p>\n
    \n  Now, you can save the design and your table will be converted to date and the SQL convert date is completed:\n<\/p>\n
    \n  \"SQL
    Conversion functions<\/h2>\n
    \n  T-SQL contains functions to convert data types. We will use CAST and CONVERT to do a SQL convert date.\n<\/p>\n
    \n  Let\u2019s start with CAST first:\n<\/p>\n
    How to convert from varchar, nvarchar, char, nchar to sql date using CAST<\/h2>\n
    \n  The following example, will show how to convert characters to a datetime date type using the CAST function:\n<\/p>\n
    declare @vardate varchar(100)='03-04-2016'\r\nselect CAST(@vardate AS datetime) AS dataconverted;<\/pre>\n<\/p>\n
    \n  The example declares a variable named vardate and then this variable that is a varchar is converted to datetime using the CAST function.\n<\/p>\n
    Note:<\/em><\/strong> For more information about the CAST function, refer to this link: <\/em>CAST and CONVERT (Transact-SQL)<\/em><\/a>\n<\/p>\n
    How to do a SQL convert date from varchar, nvarchar, char, nchar to date using CONVERT<\/h2>\n
    \n  CONVERT is a function that can do the same than CAST in the previous scenario.\n<\/p>\n
    declare @vardate varchar(100)='03-04-2016'\r\nselect CONVERT(datetime, @vardate) as dataconverted<\/pre>\n<\/p>\n
    \n  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.\n<\/p>\n
    \n  For example, if you want the date in the ISO format, you can use the following T-SQL sentence:\n<\/p>\n
    select CONVERT(nvarchar(30),getdate(), 121) as isoformat<\/pre>\n<\/p>\n
    How to convert sql date into different formats in T-SQL<\/h2>\n
    \n  The following example shows how to convert the date format in different formats.\n<\/p>\n
    \n  For Japananes format:\n<\/p>\n
    select CONVERT(nvarchar(30),getdate(), 111) as Japanformat<\/pre>\n<\/p>\n
    \n  For USA format:\n<\/p>\n
    select CONVERT(nvarchar(30),getdate(), 110) as USAformat<\/pre>\n<\/p>\n
    \n  For ANSI format:\n<\/p>\n
    select CONVERT(nvarchar(30),getdate(), 102) as ANSIformat<\/pre>\n<\/p>\n
    \n  For British format:\n<\/p>\n
    select CONVERT(nvarchar(30),getdate(), 103) as Britishformat<\/pre>\n<\/p>\n
    \n  For German format:\n<\/p>\n
    select CONVERT(nvarchar(30),getdate(), 104) as Germanformat<\/pre>\n<\/p>\n
    \n  For Italian format:\n<\/p>\n
    select CONVERT(nvarchar(30),getdate(), 105) as Italianformat<\/pre>\n<\/p>\n
    \n  For European default format:\n<\/p>\n
    select CONVERT(nvarchar(30),getdate(), 113) as EuropeDefaultformat<\/pre>\n<\/p>\n
    \n  For ODBC Canonical:\n<\/p>\n
    select CONVERT(nvarchar(30),getdate(), 120) as ODBCCanonicalformat<\/pre>\n<\/p>\n
    \n  You always have the option to use the FORMAT function to get the date in the format that you want:\n<\/p>\n
    SELECT FORMAT( getdate(), 'dd\/MM\/yy')<\/pre>\n<\/p>\n
    \n  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.\n<\/p>\n
    Note:<\/em><\/strong> For more information about the FORMAT function, refer to this link: <\/em>FORMAT (Transact-SQL)<\/em><\/a>\n<\/p>\n
    Problems related to SQL convert date operations<\/h2>\n
    \n  When you try to convert to date it is not always possible. The following example shows a common error:\n<\/p>\n
    declare @vardate varchar(100)='11242016'\r\nselect CONVERT(datetime, @vardate) as dataconverted<\/pre>\n<\/p>\n
    \n  The error message is the following:\n<\/p>\n
    Msg 242, Level 16, State 3, Line 22<\/span>\n<\/p>\n
    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.<\/strong>\n<\/p>\n
    \n  You need separators for the date like a \u201c\/\u201d, a \u201c.\u201d or a \u201c-\u201c.\n<\/p>\n
    \n  The following example, modifies the string from 11242016 to 11-24-2016 and then converts to sql date:\n<\/p>\n
    declare @vardate varchar(100)='11242016'\r\n\r\nset @vardate= SUBSTRING(@vardate, 1, 2)+'-'+ SUBSTRING(@vardate, 3, 2)+'-'+SUBSTRING(@vardate, 5, 4)\r\n\r\nselect CONVERT(date, @vardate) as dataconverted<\/pre>\n<\/p>\n
    \n  We use substring to concatenate the \u201c-\u201d to use an acceptable date format and then we use the CONVERT function to convert the characters to sql date.\n<\/p>\n
    Date data types<\/h2>\n
    \n  In SQL Server, there are several types of date datatypes:\n<\/p>\n