Problem with stored procedure inserting date format
I have stored procedure that needs to insert date into table. But when I try to execute it through graphical interface with YYYY-MM-DD format I'm getting error saying:
incorrect syntax near '-'.
I also tried with apostrophe but then I'm getting error converting data type numeric to int. I checked if my table has right format, and it does. Any idea what is happening?
ALTER PROC [dbo].[UbaciVlasnika] (@ImeVlasnika NVARCHAR(50), @PrezimeVlasnika NVARCHAR(50), @DatumRodjenja DATE, @JMBG INT, @VlasnikID INT OUTPUT) AS INSERT INTO Vlasnik VALUES (@ImeVlasnika, @PrezimeVlasnika, @DatumRodjenja, @JMBG) SELECT @VlasnikID = SCOPE_IDENTITY()
PS: I also tried to insert values with
INSERT INTO Vlasnik VALUES ('xxxxx', 'xxxx', 'YYYY-MM-DD', 'xxxxx')
and it's working.
When executing this via the GUI, you need to use single quotes around the date you input, e.g.,
If you take a look at the code in the resulting query editor, you'll notice that if you don't add the quotes, it will look something like:
@DatumRodjenja = 2019-10-08
This causes the "Incorrect syntax near '-'." error.
Also, the value you have specified for the
int parameter is too big for the
int data type, you may need to look into using
date format in stored procedure – SQLServerCentral, date format in stored procedure – Learn more on the insert into TB_StatusText values(@Statustext,@RecievedBy,@Deliverydate). END. i want to convert the Delivery date to mm/dd/yyyy format but am getting error as. specify the date in YYYY-MM-DD format. exec new_proc @from_date = '2011-04-01', @to_date = '2011-04-30'
You should always list the column names when you are doing an
ALTER PROC [dbo].[UbaciVlasnika] ( @ImeVlasnika nvarchar (50), @PrezimeVlasnika nvarchar (50), @DatumRodjenja date, @JMBG int, @VlasnikID int OUTPUT ) AS BEGIN INSERT INTO Vlasnik (ImeVlasnika, PrezimeVlasnika, DatumRodjenja, JMBG) VALUES (@ImeVlasnika, @PrezimeVlasnika, @DatumRodjenja, @JMBG); SELECT @VlasnikID = SCOPE_IDENTITY() END; -- UbaciVlasnika
I am guessing what the column names are.
Clearly, you are not inserting into all the columns, because one of them is the identity column. I'm not sure why you are not getting an error that says that you have the wrong number of parameter values. Perhaps SQL Server checks types before it counts all the values.
[Solved] insert date in sql server as "dd-mm-yyyy" format, So you do not store date in SQL by any format - only displays it in Dates should be stored using the appropriate Date/DateTime types, Probably you are performing a wrong conversion either from string to date or from date "@date date" is my stored procedure's parameter and "dob" is my table's field Stack Overflow Public questions Problem with stored procedure inserting date format data type numeric to int even if my column and SP parameter was date format.
The problem is actually in your GUI. The code should really look something like this (C# used for example):
var cmd = new SqlCommand("UbaciVlasnika", "connstr"); cmd.CommandType=CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ImeVlasnika", "some text"); cmd.Parameters.AddWithValue("@PrezimeVlasnika", "some text"); //ATTENTION! parse the string to date type; don't pass a string into to a date type parameter cmd.Parameters.AddWithValue("@DatumRodjenja", DateTime.ParseExact("2019-01-01", "yyyy-MM-dd", CultureInfo.InvariantCulture)); //OR IF YOU ALREADY HAVE A DATE cmd.Parameters.AddWithValue("@DatumRodjenja", myDateTimePicker.Value); cmd.Parameters.AddWithValue("@JMBG", 12345); cmd.Parameters.Add("@VlasnikID", SqlDbType.Int).Direction = ParameterDirection.Output; con.Open(); int returnCode = cmd.ExecuteNonQuery(); con.Close(); MessageBox.Show("Output was " + (int)cmd.Parameters["@VlasnikID"].Value);
In the client side app we parse the parameters explicitly, to ensure that we're passing the right datatype to the server, rather than relying on implicit conversion. The db server might be in a different region to the app that is calling this procedure and hence have a different notion of how dates are formatted. The client app should always do the donkey work of parsing the argument, unless you change the signature of your procedure so it accepts a varchar and specifically convert the date inside the procedure - I'd avoid doing this for aforementioned reasons about the front end knowing/caring about regions and localisation and culture etc whereas the back end db can/should remain ignorant of these things
If you're using SSMS to quickly test this SP, do this:
DECLARE @return_value int; DECLARE @VlasnikID int; DECLARE @im NVARCAHR(50) = N'Petar'; DECLARE @pv NVARCAHR(50) = N'Petrović'; DECLARE @dr DATE = '1897-03-20'; --or CONVERT(date, '1897-03-20', 20) DECLARE @jm INT = 2003897710341; --this is way larger than int's max value 2147483647 - change it! EXEC @return_value = [dbo].[UbaciVlasnika] @ImeVlasnika = @im, @PrezimeVlasnika = @pv, @DatumRodjenja = @dr, @JMBG = @jm, @VlasnikID = @VlasnikID OUTPUT; SELECT @VlasnikID as VlasnikID, @return_value as ReturnValue
Note that you're trying to stuff ~2 trillion into an int, whose max value is ~2 billion
Disclaimer: For clarity, when I said "should look something like this" I mean to treat this as pseudocode example - not that this was the perfect ideal code that should be copypasta'd straight into your production system.
Here I'm making the point to convert your data in the client, not rely on the implicit conversion on the server. I'm not specifically advocating use of AddWithValue; AWV makes for a great way to write some nice clean, simple parameterization code for a SqlCommand as an example on SO, though it opens up a Pandora's box of its own - consider whether you want it in a production system. It matters less with stored procedures because the parameter type that is used inside the procedure is defined in the procedure signature rather than inferred from the type inferred from the .net type passed to AddWithValue so I'm more relaxed about it's use with SPs to the point where I don't usually draw attention to it unless people start pitchforking about it
Server stored procedure to convert date format before insert/ update, The source which has date in dd-MMM-yy format, have to convert it to yyyy-mm-dd hh:mm:ss:SS and insert/update into the database using stored procedure. It helps others to find relevant posts to the same question. Mix Create a stored procedure and insert instructions into Sql Server I created a number of scripts to be run separately but was asked to combine them all so the DBA only has to do it once. The problem is that I cannot seem to combine them to run together. Only the first item in the query gets run. How do I format thes
Insert and Select Date in dd/MM/yyyy format in SQL Server, This article will illustrate how to use Stored Procedures for inserting and selecting Dates in dd/MM/yyyy format. TAGs: SQL Server, Stored Insert data in SQL Server stored procedure Tag: stored-procedures , sql-server-2012 I am trying to add data in single table using a SQL Server stored procedure and I am getting an error
Date format in sql stored procedure, When I insert the date time via asp.net and the sql string is in my codebehind file (none stored procedure) it puts the date in DD/MM/YYYY format into Whats your problem with this? which format you want and which format Inside the Insert_Medicine Stored Procedure, the Date parameters are of VARCHAR data type so that the Date values can be passed in dd/MM/yyyy format. Before the INSERT statement, the DATEFORMAT command is executed with DMY option which notifies SQL Server that the values of Dates will be in dd/MM/yyyy format.
Passing Date Parameters to Stored Procedure, I then tried to insert it into a database with fields of type date. If you still have an error message telling the value can't be parsed you shouldn't Hi How to Insert date in 'DD/MM/YYYY' format in oracle using stored procedure? But using stored procedure, create or replace procedure Date_Test