datediff issue in stored procedure

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 2000 Forums

SQL Server Development (2000)

datediff issue in stored procedure

Author

Topic

lleemon
Starting Member

24 Posts

Posted - 2004-10-21 : 16:30:52

I have the folowing stored procedure and am calling it from a .aspx (ASP.NET) file. I keep getting the following error: 'Syntax error converting datetime from character string.' But when I just enter the following line in Query Analyser it works fine: sc_add_referer '66.0.37.196', 'ex041017.log', 'test', '2004-06-23 10:17:23'

I have found out that the issue is with the datediff I have below. You will see a line that says 'doesn't work' and 'does work' and when I hard code the dates it works but when pulling in doesn't.

Anyone have an idea on what I can do/try?

Thanks in advance.


CREATE PROCEDURE sc_add_referer
(
 @SENT_IP varchar(15),
 @SENT_LOG_FILE varchar(15),
 @SENT_REFERER text,
 @SENT_DT_TM varchar(30)
)
 AS
DECLARE @ROW_CNT INT
DECLARE @ROW_ORDERID INT
DECLARE @ROW_DT_TM INT
DECLARE @SUB_CNT INT

--FIRST NEED TO FIND OUT IF IN MAIN TABLE
SELECT @ROW_CNT = count(*), @ROW_ORDERID = ORDER_ID
FROM sc_ip_tracking
WHERE IP = @SENT_IP
GROUP BY ORDER_ID

SELECT @SUB_CNT = count(*) 
FROM sc_ip_tracking_referers 
WHERE ORDER_ID = @ROW_ORDERID
AND LOG_FILE = @SENT_LOG_FILE

IF @ROW_CNT > 0
  -- IP FOUND SO SEE IF FOUND IN REFERER LOG
  IF @SUB_CNT > 0
    --ROW ROUND
    RETURN 0
  ELSE
    -- NO ROW FOUND SO NEED TO ADD INFO
   BEGIN

 -- DOESN'T WORK
     	--SELECT @ROW_DT_TM =  datediff(hour, @SENT_DT_TM, DT_TM) FROM sc_ip_tracking WHERE IP = @SENT_IP AND ORDER_ID = @ROW_ORDERID

-- DOES WORK
     	SELECT @ROW_DT_TM =  datediff(hour, '2004-06-24 19:17:23', '2004-06-23 10:17:23.000')  FROM sc_ip_tracking WHERE IP = @SENT_IP AND ORDER_ID = @ROW_ORDERID


   	 --PRINT @ROW_DT_TM

   IF @ROW_DT_TM >= 0 AND @ROW_DT_TM <= 8
    BEGIN
    INSERT INTO sc_ip_tracking_referers (ORDER_ID, LOG_FILE, REFERER, LAST_CHANGE_DT)
    values (@ROW_ORDERID, @SENT_LOG_FILE, @SENT_REFERER, getdate())
    END
    RETURN -2
    END

ELSE
  RETURN 0
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-21 : 16:42:46

@SENT_DT_TM should be defined as datetime and not varchar(30).

Tara

lleemon
Starting Member

24 Posts

Posted - 2004-10-21 : 16:50:30

I have tried converting @SENT_DT_TM to datetime and I get the following error then:
'Error converting data type nvarchar to datetime.'


CREATE PROCEDURE sc_add_referer
(
 @SENT_IP varchar(15),
 @SENT_LOG_FILE varchar(15),
 @SENT_REFERER text,
 @SENT_DT_TM datetime
)
 AS
DECLARE @ROW_CNT INT
DECLARE @ROW_ORDERID INT
DECLARE @ROW_DT_TM INT
DECLARE @SUB_CNT INT

--FIRST NEED TO FIND OUT IF IN MAIN TABLE
SELECT @ROW_CNT = count(*), @ROW_ORDERID = ORDER_ID
FROM sc_ip_tracking
WHERE IP = @SENT_IP
GROUP BY ORDER_ID

SELECT @SUB_CNT = count(*) 
FROM sc_ip_tracking_referers 
WHERE ORDER_ID = @ROW_ORDERID
AND LOG_FILE = @SENT_LOG_FILE

IF @ROW_CNT > 0
  -- IP FOUND SO SEE IF FOUND IN REFERER LOG
  IF @SUB_CNT > 0
    --ROW ROUND
    RETURN 0
  ELSE
    -- NO ROW FOUND SO NEED TO ADD INFO
   BEGIN

     	SELECT @ROW_DT_TM =  datediff(hour, @SENT_DT_TM, DT_TM) FROM sc_ip_tracking WHERE IP = @SENT_IP AND ORDER_ID = @ROW_ORDERID
	

   IF @ROW_DT_TM >= 0 AND @ROW_DT_TM <= 8
    BEGIN
    INSERT INTO sc_ip_tracking_referers (ORDER_ID, LOG_FILE, REFERER, LAST_CHANGE_DT)
    values (@ROW_ORDERID, @SENT_LOG_FILE, @SENT_REFERER, getdate())
    END
    RETURN -2
    END

ELSE
  RETURN 0
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-21 : 16:52:58

What data type is DT_TM in sc_ip_tracking?

Tara

lleemon
Starting Member

24 Posts

Posted - 2004-10-21 : 17:00:52

DT_TM is datetime (8)

quote:
Originally posted by tduggan

What data type is DT_TM in sc_ip_tracking?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-21 : 17:04:37

I'm confused by the fact that you said this is working:

sc_add_referer '66.0.37.196', 'ex041017.log', 'test', '2004-06-23 10:17:23'

If the call to the stored procedure is working, when is it failing?

Tara

lleemon
Starting Member

24 Posts

Posted - 2004-10-21 : 17:13:51

Yes, it works if I hardcode values but when I set to 'SELECT @ROW_DT_TM = datediff(hour, @SENT_DT_TM, DT_TM) FROM sc_ip_tracking WHERE IP = @SENT_IP AND ORDER_ID = @ROW_ORDERID' it returns my error in my .net application.

quote:
Originally posted by tduggan

I'm confused by the fact that you said this is working:

sc_add_referer '66.0.37.196', 'ex041017.log', 'test', '2004-06-23 10:17:23'

If the call to the stored procedure is working, when is it failing?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-21 : 17:21:18

DECLARE @SENT_DT_TM datetime

SET @SENT_DT_TM = '2004-06-23 10:17:23'

SELECT datediff(hour, @SENT_DT_TM, DT_TM)
FROM sc_ip_tracking

Does this work in Query Analyzer?

Does the call to the stored procedure work in Query Analyzer when you don't hard code values?

Tara

lleemon
Starting Member

24 Posts

Posted - 2004-10-21 : 17:35:44

tduggan-Thanks for all your help so far.


DECLARE @SENT_DT_TM datetime
SET @SENT_DT_TM = '2004-06-23 10:17:23'
SELECT datediff(hour, @SENT_DT_TM, DT_TM), @SENT_DT_TM, DT_TM FROM sc_ip_tracking

I get the following results.

0 2004-06-23 10:17:23.000 2004-06-23 10:17:48.000
1 2004-06-23 10:17:23.000 2004-06-23 11:42:41.000
1 2004-06-23 10:17:23.000 2004-06-23 11:44:29.000

So this must mean my stored procedure is working and probably has something to do with my calling it from my .net app.

quote:
Originally posted by tduggan

DECLARE @SENT_DT_TM datetime

SET @SENT_DT_TM = '2004-06-23 10:17:23'

SELECT datediff(hour, @SENT_DT_TM, DT_TM)
FROM sc_ip_tracking

Does this work in Query Analyzer?

Does the call to the stored procedure work in Query Analyzer when you don't hard code values?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-21 : 17:37:43

Yes. You need to figure out what you are passing to the stored procedure. You can do this with SQL Profiler.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources