Conversion error while converting date and / or time from character string - Visual Studio
I am trying to insert data into my database using these commands (Homework):
SET IDENTITY_INSERT [dbo].[Movies] ON
INSERT INTO [dbo].[Movies] ([Id], [CategoryId], [Title], [Director], [DateReleased])
VALUES (1, 3, N'Titanic', N'James Cameron', N'1997‐06‐21 00:00:00')
INSERT INTO [dbo].[Movies] ([Id], [CategoryId], [Title], [Director], [DateReleased])
VALUES (2, 1, N'StarWars', N'George Lucas', N'1977-06‐01 00:00:00')
INSERT INTO [dbo].[Movies] ([Id], [CategoryId], [Title], [Director], [DateReleased])
VALUES (3, 1, N'Jurassic Park', N'Steven Spielberg', N'1993‐06‐17 00:00:00')
INSERT INTO [dbo].[Movies] ([Id], [CategoryId], [Title], [Director], [DateReleased])
VALUES (4, 4, N'Jaws', N'Steven Spielberg', N'1975-05-30 00:00:00')
INSERT INTO [dbo].[Movies] ([Id], [CategoryId], [Title], [Director], [DateReleased])
VALUES (5, 3, N'Ghost', N'Jerry Zucker', N'1990‐06-14 00:00:00')
INSERT INTO [dbo].[Movies] ([Id], [CategoryId], [Title], [Director], [DateReleased])
VALUES (7, 3, N'Forrest Gump', N'Robert Zemeckis', N'1994‐06‐18 00:00:00')
INSERT INTO [dbo].[Movies] ([Id], [CategoryId], [Title], [Director], [DateReleased])
VALUES (8, 2, N'Ice Age', N'Chris Wedge', N'2002‐06‐26 00:00:00')
INSERT INTO [dbo].[Movies] ([Id], [CategoryId], [Title],[Director], [DateReleased])
VALUES (9, 2, N'Shrek', N'AndrewAdamson', N'2001‐06‐25 00:00:00')
INSERT INTO [dbo].[Movies] ([Id], [CategoryId], [Title],[Director], [DateReleased])
VALUES (10, 1, N'Independence Day', N'Roland Emmerich', N'1996-06-20 00:00:00')
INSERT INTO [dbo].[Movies] ([Id], [CategoryId], [Title], [Director], [DateReleased])
VALUES (11, 4, N'The Ring', N'Gore Verbinski', N'2002‐06‐26 00:00:00')
SET IDENTITY_INSERT [dbo].[Movies] OFF
But it leaves me with an exception:
Conversion error when converting date and / or time from character string
source to share
There are many formats supported by SQL Server - see the MSDN online books on CAST
andCONVERT
. Most of these formats depend on what settings you have, so these settings may work multiple times and sometimes not.
The way around this problem is to use the (slightly adapted) ISO-8601 format supported by SQL Server - this format always works - regardless of your SQL Server and your date and time setting.
The ISO-8601 format is supported by SQL Server in two flavors:
-
YYYYMMDD
only for dates (no time part); note here: no dash! , it is very important! DoesYYYY-MM-DD
NOT depend on dateformat parameters on your SQL Server and will NOT work in all situations!
or
-
YYYY-MM-DDTHH:MM:SS
for dates and times - note: this format has dashes (but can be omitted) and fixedT
as a separator between your date and timeDATETIME
.
This is true for SQL Server 2000 and later.
If you are using SQL Server 2008 or newer and the datatype DATE
(only DATE
- not DATETIME
!), You can also use the format YYYY-MM-DD
and it will also work with any setting on your SQL Server.
Don't ask me why this whole topic is so complex and somewhat confusing - it is. But with the format, YYYYMMDD
you should be fine for any version of SQL Server and for any language and dateformat setting on your SQL Server.
The recommendation for SQL Server 2008 and newer is to use DATE
when you only need a part of the date, and DATETIME2(n)
when you need the date and time. You should try to start phase-out of the datatype DATETIME
if possible.
So in your example code - does this insert work? (I added a separator T
between the date YYYY-MM-DD
and the time part)
INSERT INTO [dbo].[Movies] ([Id], [CategoryId], [Title], [Director], [DateReleased])
VALUES (11, 4, N'The Ring', N'Gore Verbinski', N'2002‐06‐26T00:00:00')
Or, if you are interested in the date itself (not the time), try this:
INSERT INTO [dbo].[Movies] ([Id], [CategoryId], [Title], [Director], [DateReleased])
VALUES (11, 4, N'The Ring', N'Gore Verbinski', N'20020626')
source to share