Problem with date in sp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problem with date in sp

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-01-27 : 09:35:28

I have just started a new db on sqlserver 2008 and am having problems inserting records with dates. Here is my sp which runs fine in 2005, but when I execute it in 2008 I get the error

Error converting data type nvarchar to numeric.
and this is highlighted in the pane above.

@strDateReq = N'02/02/11'

ALTER PROCEDURE [dbo].[spSM_AddWeeks]

@strDateReq  nvarchar (20)
AS

INSERT INTO tblSM_Meals(
ML_ID,
ML_WeekEnding,
ML_SchoolNumber)


SELECT 
@strDateReq +'"*"'+ SC_SNo,
@strDateReq,
SC_SNo
from tblSM_Schools

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-27 : 09:40:38

Why are you using nvarchar datatype for date value? Use proper DATETIME datatype

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-01-27 : 09:47:15

I've tried that - I've also tried just DATE - 2008 has this type for date only which is all I want, not the time

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-01-27 : 09:52:12

I just tried that and got thi serror

Conversion failed when converting date and/or time from character string.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-27 : 09:53:58

Can you post the code that you tried with DATETIME or DATE datatype? Also what is the datatype of the column?

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-01-27 : 09:57:41

ML_ID nvarchar
ML_Weekending date
ML_SNo numeric

I am right clicking on the sp in the object explorer and selecting execute tored procedure and entering the date parameter

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-27 : 09:59:35

What happens when you do this?

EXEC spSM_AddWeeks '20110202'

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-01-27 : 10:02:58

Conversion failed when converting date and/or time from character string.

My server is British US ( I waiting for our server team to change it to British English for me) That is why I chose the date I did - it could a valid date for either so should not throw an error.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-27 : 10:07:00

What is the datatype of the parameter @strDateReq?

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-01-27 : 10:13:38

datetime

if I make it date I get this error when I try to run it to alter it.

The data types date and varchar are incompatible in the add operator.

If i change it to varchar I get this error when I execute it

Error converting data type varchar to numeric.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-27 : 10:17:00

If it is datetime datatype how can you concatenate with other columns without converting it to varchar datatype?

Your select statement sgould be

SELECT
convert(char(8),@strDateReq,112) +'"*"'+ SC_SNo,
@strDateReq,
SC_SNo
from tblSM_Schools

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-01-27 : 10:23:35

I am still getting this (sorry)

Error converting data type varchar to numeric.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-27 : 10:31:33

If SC_SNo is a number, you'll have to convert it to a string as well

Jim

Everyday I learn something that somebody else already knew

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-01-27 : 10:38:03

Thanks Jim - it is actually writing it to the table now, but in this format

20110402"*"2004 2011-04-02

I want

02/04/11*2004 02/04/11

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-01-27 : 10:45:56

I've sorted the first bit, but this bit is saved in the table as 2010-06-11
ML_ID as 11/06/2010*2004 - which is what I want

convert(char(10),@strDateReq,101),

SELECT
convert(char(10),@strDateReq,101) +'*'+ convert(varchar,SC_SchoolNumber),
convert(char(10),@strDateReq,101),
Sc_SchoolNumber
from tblSM_Schools

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-27 : 10:48:48

So @strDateReq is a date, and SC_SNo is a number? Can you give a sample value for each?

Jim

Everyday I learn something that somebody else already knew

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-01-27 : 10:52:34

@strDateReq - 24/06/10
SC_SNo - 2014

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-27 : 11:01:03

SET DATEFORMAT DMY

DECLARE @strDateReq datetime--nvarchar(20)
DECLARE @SC_Sno int
SET @strDateReq = N'24/06/10'
SET @SC_SNo = 2014

SELECT convert(varchar(10),@strDateReq,103) + '*' + convert(varchar(10),@SC_Sno)

Jim

Everyday I learn something that somebody else already knew

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-01-27 : 11:07:15

I will try that tomorrow - time difference here so off home :-)

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-01-28 : 04:44:10

No, that doesn't work either

SET DATEFORMAT DMY

returns an error - ie underlined in red

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-28 : 05:24:43