stored procedure and date format
-
hi I m passing the date parameter to a stored procedure. The stored procedure not does not accept the date format as 'dd/mm/yyyy'. It gives an error -"error converting data type varchar to datetime". My machine's regional setting is set to 'UK' i.e. "dd/mm/yyyy". How can i make a change in SQL server so that it accepts date parameters in the "dd/mm/yyyy". please help shelly
-
hi I m passing the date parameter to a stored procedure. The stored procedure not does not accept the date format as 'dd/mm/yyyy'. It gives an error -"error converting data type varchar to datetime". My machine's regional setting is set to 'UK' i.e. "dd/mm/yyyy". How can i make a change in SQL server so that it accepts date parameters in the "dd/mm/yyyy". please help shelly
-
hi I m passing the date parameter to a stored procedure. The stored procedure not does not accept the date format as 'dd/mm/yyyy'. It gives an error -"error converting data type varchar to datetime". My machine's regional setting is set to 'UK' i.e. "dd/mm/yyyy". How can i make a change in SQL server so that it accepts date parameters in the "dd/mm/yyyy". please help shelly
1. Don't concatenate parameters in your command string - use a SqlParameter object (ADO.NET) or the Parameters collection of the Command object (ADO). 2. If you must use a literal date, use the ISO format 'yyyymmdd'. SQL Server will always accept this format, regardless of locale settings. Stability. What an interesting concept. -- Chris Maunder
-
hi I m passing the date parameter to a stored procedure. The stored procedure not does not accept the date format as 'dd/mm/yyyy'. It gives an error -"error converting data type varchar to datetime". My machine's regional setting is set to 'UK' i.e. "dd/mm/yyyy". How can i make a change in SQL server so that it accepts date parameters in the "dd/mm/yyyy". please help shelly
I never pass any dates to a SQL server I always pass DD MM YY then build the date on the fly in the SQL server Strored Proc. Example below. ########################################################################################### CREATE PROCEDURE usp_myproc @YearInt int, @MonthInt int, @DayInt int AS DECLARE @CD_DATE datetime DECLARE @TempVarChar varchar(50) Set @TempVarChar = CAST(@DayInt As Varchar(2) ) + '/' + CAST(@MonthInt As Varchar(2) ) + '/' + CAST(@YearInt As Varchar(4) ) Set @CD_DATE = (Convert( datetime, @TempVarChar, 103 )) SELECT @CD_DATE go