How to enter a Date into a table in TSQL? (Error converting data type varchar to datetime)

Question

I want to enter 30/10/1988 as the date to a DOB column in a table using a procedure

alter procedure addCustomer 
@userName varchar(50),
@userNIC varchar(50), 
@userPassword varchar(100), 
@userDOB datetime, 
@userTypeID int, 
@userEmail varchar(50), 
@userTelephone int, 
@userAddress char(100),
@userCityID int,
@status int output
as

    declare @userID int
    declare @eid int
    declare @tid int
    declare @aid int

    execute getLastRaw 'userID','tblUserParent', @userID output

    insert into tblUserParent values (@userID, @userName, @userNIC, @userPassword, @userDOB, @userTypeID)

    execute getLastRaw 'addressID','tblAddress', @aid output

    insert into tblAddress values (@aid, @userAddress, @userID, @userCityID)

    execute getLastRaw 'emailID','tblEmail', @eid output

    insert into tblEmail values (@eid, @userEmail, @userID)

    execute getLastRaw 'telephoneID','tblTelephoneNO', @tid output

    insert into tblTelephoneNO values (@tid, @userTelephone , @userID)
    insert into tblUserCustomer values (@userID, @eid , @tid, @aid)

...but it gives an error when i enter like this '30/10/1988'

Msg 8114, Level 16, State 5, Procedure addCustomer, Line 0 Error converting data type varchar to datetime.

...but when I enter like only the 30/10/1988 Incorrect syntax near '/'

How do I fix this?

Answer 1

If you would truly like to avoid the possibility of ambiguous dates based, then you should always enter it in one of the two unambiguous date formats Answer has already been selected and it's valid but I'm a believer in spreading the knowledge ;)

As noticed by @cloud and my post representing a younger, and less wise me with a link only answer, I'll pop the contents of the archive of Jamie Thompson's answer for unambiguous date formats in TSQL

tl;dr;

• yyyy-MM-ddTHH24:mi:ss
• yyyyMMdd HH24:mi:ss

One of the most commonly used data types in SQL Server is [datetime] which unfortunately has some vagaries around how values get casted. A typical method for defining a [datetime] literal is to write it as a character string and then cast it appropriately. The cast syntax looks something like this: DECLARE @dt NVARCHAR(19) = '2009-12-08 18:00:00';

SELECT CAST(@dt AS datetime);

Unfortunately in SQL Server 2005 the result of the cast operation may be dependent on your current language setting. You can discover your current language setting by executing: SELECT @@LANGUAGE To demonstrate how your language setting can influence the results of a cast take a look at the following code: ALTER DATABASE tempdb

SET COMPATIBILITY_LEVEL = 90 ; --Behave like SQL Server 2005

USE tempdb

GO

DECLARE @t TABLE (

  dateString  NVARCHAR(19)

);

INSERT @t (dateString)

VALUES ('2009-12-08 18:00:00') --'yyyy-MM-dd hh24:mi:ss'

, ('2009-12-08T18:00:00') --'yyyy-MM-ddThh24:mi:ss'

, ('20091208 18:00:00') --'yyyyMMdd hh24:mi:ss'

SET LANGUAGE french;

SELECT 'french' AS lang

, DATENAME(MONTH,q.[dt]) AS mnth

, q.[dt]

FROM (

   SELECT  CAST(dateString AS DATETIME) AS dt

   FROM    @t

   )q;

SET LANGUAGE us_english;

SELECT 'us_english' AS lang

, DATENAME(MONTH,q.[dt]) AS mnth

, q.[dt]

FROM (

   SELECT  CAST(dateString AS DATETIME) AS dt

   FROM    @t

   )q; We are taking the value which can be described in words as “6pm on 8th December 2009”, defining it in three different ways, then

seeing how the @@LANGUAGE setting can affect the results. Here are those results: french language datetime Notice how the interpretation of the month can change depending on @@LANGUAGE. If @@LANGUAGE=’french’ then the string '2009-12-08 18:00:00' is interpreted as 12th August 2009 (‘août’ is French for August for those that don’t know) whereas if @@LANGUAGE=’us_english’ it is interpreted as 8th December 2009. Clearly this is a problem because the results of our queries have a dependency on a server-level or connection-level setting and that is NOT a good thing. Hence I recommend that you only define [datetime] literals in one of the two unambiguous date formats: yyyy-MM-ddTHH24:mi:ss yyyyMMdd HH24:mi:ss That was going to be the end of this blog post but then I found out that this behaviour changed slightly in SQL Server 2008. Take the following code (see if you can figure out what the results will be before I tell you): ALTER DATABASE tempdb

SET COMPATIBILITY_LEVEL = 100 ; --Behave like SQL Server 2008

GO

USE tempdb

GO

SET LANGUAGE french;

DECLARE @dt NCHAR(10) = '2009-12-08 18:00:00'; --Ambiguous date format

SELECT CAST(@dt AS datetime) AS [ExplicitCast]

, DATENAME(MONTH,@dt) AS [MonthFromImplicitCast]

, DATENAME(MONTH,CAST(@dt AS datetime)) AS [MonthFromExplicitCast]; Here we are doing three different things with our nchar literal: explicitly cast it as a [datetime] extract the month name from the char literal using the DATENAME function (which results in an under-the-covers implicit cast) extract the month name from the char literal using the DATENAME function after it has been explicitly casted as a [datetime] Note that the compatibility level is set to SQL Server 2008 and @@LANGUAGE=’french’. Here are the results: image (Were you correct?) Let’s take a look at what is happening here. The behaviour when we are explicitly casting as [datetime] hasn’t changed, our nchar literal is still getting interpreted as 12th August rather than 8th December when @@LANGUAGE=’french’. The [MonthFromExplicitCast] field is interesting though, it seems as though the implicit cast has resulted in the desired value of 8th December. Why is that? To get the answer we can turn to BOL’s description of the DATENAME function syntax: image The implicit cast is not casting to [datetime] at all, it is actually casting to [date] which is a new datatype in SQL Server 2008. The new date-related datatypes in SQL Server 2008 (i.e. [date], [datetime2], [time], [datetimeoffset]) disregard @@LANGUAGE and hence we get behaviour that is more predictable and, frankly, better. These new behaviours for SQL Server 2008 were unknown to me when I began this blog post so I have learnt something in the course of authoring it, I hope it has helped you too. No doubt someone somewhere is going to get nastily burnt by this at some point, make sure that it isn’t you by always using unambiguous date formats: yyyy-MM-ddTHH24:mi:ss yyyyMMdd HH24:mi:ss regardless of which version you are on!

Answer 2

The following works in both SQL Server and MySql without ambiguity: yyyy-mm-dd, like so:

INSERT INTO TableName(DateColumn) VALUES ('1988-10-30');

...as an added benefit there's no question of whether it's a US or European style date on days like the fourth of March...