Depending on your regional settings, the parameter you are passing in for @TimeItemAdded might not be recognized.
You should pass the date in as:
20120720
Depending on your regional settings, the parameter you are passing in for @TimeItemAdded might not be recognized.
You should pass the date in as:
20120720
Use an unambiguous string literal for your date. In this case:
EXEC dbo.InsertItemDetails
...
, @TimeItemAdded = '20120720';
Better yet, make sure to pass a strongly typed parameter where you know that the date is correct. Ideally, this should never be presented as a string in any format.
Regional formats like m/d/y are bad news, because you can't ensure that they will work given the user's session, dateformat, language settings, the regional settings on the machine, etc.
Try CONVERT(DATETIME, @PaymentDate, 103)
104 is the German style which uses periods between the numerals, as opposed to slashes. 103 is the British/French style.
See: https://msdn.microsoft.com/en-us/library/ms187928.aspx
I've noticed your question is also tagged with c#.
If you are passing the date from c# to sql server,
Don't pass dates as strings. Pass them as DateTime.
The .Net DateTime maps directly to SQL Server's DateTime.
This way, you will not have to deal with the display format at all, since both c# and SQL Server does not store display format in DateTime.
If you really need to convert the string '26/01/2017' to date, you should use 103 for your style argument, as already suggested in other answer.
Error converting data type nvarchar to datetime??? – SQLServerCentral Forums
[RESOLVED]SSRS Error converting Datatype NVARCHAR to DATETIME / Too many Arguments Specified
sql - Calling stored procedure Error: Error converting data type nvarchar to datetime - Stack Overflow
SQL Server : error converting data type nvarchar to datetime - Stack Overflow
Since you are trying to pass in table name (not in your question but mentioned in the comments) you do need dynamic sql because table names cannot be parameterized. However, you can pass in the name as a parameter to your procedure just like all the values. Then you can parameterize your dynamic sql which will eliminate all the hassle of datatype conversions and such. Something like this should be pretty close. I also added a lot of white space so this is easier to read.
ALTER PROCEDURE [dbo].[myproc]
(
@name varchar(50)
, @ID bigint
, @birthdate datetime
, @Enabled bit
, @married bit
, @employees int
, @hiredDate datetime
, @TableName sysname
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql AS NVARCHAR(MAX)
SET @sql = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE ID = @ID )
UPDATE ' + QUOTENAME(@TableName) + '
SET
name = @name
, birthdate = @birthdate
, Enabled = @Enabled
, married = @married
, employees = @employees
, hiredDate = @hiredDate
where ID = @ID
ELSE
INSERT INTO ' + QUOTENAME(@TableName) + '
(
name
, ID
, birthdate
, Enabled
, married
, employees
, hiredDate
)VALUES
(
@name
, @ID
, @birthdate
, @Enabled
, @married
, @employees
, @hiredDate
)'
EXEC sp_executesql @sql,
N'@name varchar(50)
, @ID bigint
, @birthdate datetime
, @Enabled bit
, @married bit
, @employees int
, @hiredDate datetime'
, @name = @name
, @ID = @ID
, @birthdate = @birthdate
, @Enabled = @Enabled
, @married = @married
, @employees = @employees
, @hiredDate = @hiredDate
END
There is no need for dynamic SQL at all:
ALTER PROCEDURE [dbo].[myproc]
(
@name varchar(50),@ID bigint,@birthdate datetime,@Enabled bit
,@married bit ,@employees int,@hiredDate datetime
)
AS
BEGIN
SET NOCOUNT ON
IF EXISTS(SELECT * FROM MyTable WHERE MyTable.ID =@id)
UPDATE MyTable
SET name =name
,birthdate=@birthdate
,Enabled =@Enabled
,married=@married
,employees=@employees
,hiredDate=@hiredDate
where ID = @ID;
ELSE
INSERT INTO MyTable(name,ID,birthdate,Enabled,married,employees,hiredDate)
VALUES(@name, @ID, @birthdate, @Enabled, @married, @employees, @hiredDate) ;
END
EDIT:
I run this sp with:
@birthdate = '2018-12-10 16 45 00',
I propose to provide parameter with culture independent format ISO-8601 YYYY-MM-DDThh:mm:ss
@birthdate = '2018-12-10T16:45:00',
I have faced the same problem a week ago. The problem is with the time zone setting. Specify in other formats like mm/dd/yyyy (usually works).
Specifying the date as 30/12/2013 resulted in the error for me. However, specifying it as mm/dd/yyyy format worked.
If you need to convert your input the you can try looking into the CONVERT method.
Syntax is
CONVERT(VARCHAR,@your_date_Value,103)
CONVERT(VARCHAR, '12/30/2013', 103)
The finishing 103 is the datetime format.
Refer this link for conversion formats and further reading. https://www.w3schools.com/sql/func_sqlserver_convert.asp
I ran into this issue due to a silly mistake. Make sure the date actually exists!
For example:
September 31, 2015 does not exist.
EXEC dbo.SearchByDateRange @Start = '20150901' , @End = '20150931'
So this fails with the message:
Error converting data type varchar to datetime.
To fix it, input a valid date:
EXEC dbo.SearchByDateRange @Start = '20150901' , @End = '20150930'
And it executes just fine.
'28/1/2013' is an ambiguous format - SQL Server might interpret it as dd/mm/yyyy or as mm/dd/yyyy. In this case, it's done the latter, and doesn't know what the 28th month of the year is.
Use '20130128' instead. This is always interpreted as yyyymmdd.
Had the same error. In my case it was passing datetime2 value to a datetime column:
Had to change string to datetime (3 digits) instead of 7 digits for milliseconds:
exec [dbo].[UpdateProc] @LastViewDate='2016-07-27 11:03:04.1378957'
To:
exec [dbo].[UpdateProc] @LastViewDate='2016-07-27 11:03:04.137'