CODEWITHSUNDEEP
sql-server
Luis Villarroel
Luis Villarroel

Reputation: 31

How i can insert a value type date time in my table?

The problem is in don't insert value in the table.

I check consult of SQL Server for insert values for the of type date time by not solved problem.

USE [NameTable]
GO

INSERT INTO [dbo].[User]
    ([UserSID]
    ,[ApplicationSID]
    ,[Username]
    ,[Email]
    ,[Password]
    ,[PasswordQuestion]
    ,[PasswordAnswer]
    ,[IsApproved]
    ,[IsOnline]
    ,[IsLockedOut]
    ,[LastLockedOutDate]
    ,[FailedPasswordAttemptCount]
    ,[FailedPasswordAttemptWindowStart]
    ,[FailedPasswordAnswerAttemptCount]
    ,[FailedPasswordAnswerAttemptWindowStart]
    ,[Comment]
    ,[CreationDate]
    ,[LastActivityDate]
    ,[LastLoginDate]
    ,[LastPasswordChangedDate]
    ,[LastChangedUser]
    ,[LastChangedDate])
     VALUES
    ('db0a4054-5efe-4945-bd08-ba057cd7990f'
    ,'F830DCF0-7D82-4FC1-B258-0502D0F00029'
    ,'lvillarroel'
    ,'[email protected]'
    ,'vcaMiL4tVmj4wyXhTw/ISz344maPUDzcV2z8SFVKMF0='
    ,'NULL'
    ,'NULL'
    ,1
    ,0
    ,0
    ,'2019-01-14T18:10:40'
    ,0
    ,'NULL'
    ,0
    ,'NULL'
    ,''
    ,'2019-01-14T18:10:40'
    ,'NULL'
    ,'NULL'
    ,'NULL'
    ,'db0a4054-5efe-4945-bd08-ba057cd7990f'
    ,'NULL')
GO

The result is a message

Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.

Upvotes: 2

Views: 70

Answers (2)

Suraj Kumar
Suraj Kumar

Reputation: 5643

If you will write 'NULL' then SQL Server understand and consider it as a string value. So if you want not want to insert values of Datetime then you need to simply write NULL as shown below.

Here I have used same date value as you have used in your SQL query trying to run and as in your question.

Create table Users([LastLockedOutDate]  DateTime
    ,[CreationDate]  DateTime
    ,[LastActivityDate]  DateTime
    ,[LastLoginDate]  DateTime
    ,[LastPasswordChangedDate]  DateTime
    ,[LastChangedDate] DateTime)

Insert Into Users Values('2019-01-14T18:10:40', '2019-01-14T18:10:40', NULL, NULL, NULL, NULL)

SELECT * FROM Users

You can find the demo here Live Demo

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

The real problem is 'null', you don't need to include single quote around null values.

Or you can directly omit the columns which has no value (i.e. 'null').

Your code works perfect even the dates are in 2019-01-14T18:10:40 SQL Server will implicit convert it to date/date time for you.

So, in your code you have a [LastChangedDate] & you supply 'NULL' value which is incorrect & will not implicit convert to datetime. So, try it just null instead of 'null'.

Upvotes: 4

Related Questions