SQL Server Performance Forum – Threads Archive

varible not declared…

please can someone help me i am getting the follwing message which i belive are follow up message… Server: Msg 137, Level 15, State 2, Line 9
Must declare the variable ‘@appid’.
Server: Msg 137, Level 15, State 1, Line 16
Must declare the variable ‘@appid’.
Server: Msg 137, Level 15, State 1, Line 20
Must declare the variable ‘@appid’.
Server: Msg 137, Level 15, State 1, Line 24
Must declare the variable ‘@appid’. this is when i execute the following… DECLARE @Hours int
DECLARE @Minutes int
DECLARE @Seconds int
DECLARE @TotalTime varchar(250)
DECLARE @DateRan datetime SELECT @DateRan = (SELECT DateTimeOfCall
FROM application_tbl
WHERE appid = @appid
) SET DateFormat mdy SELECT @Hours = datediff(hh,TimeBegin,GETDATE())
FROM application_tbl
WHERE appid = @appid SET @Minutes = (SELECT datediff(ss,TimeBegin,GETDATE())
FROM application_tbl
WHERE appid = @appid) SET @Seconds = (SELECT datediff(ss,TimeBegin,GETDATE())
FROM application_tbl
WHERE appid = @appid) SET @Minutes = (@Minutes / 60)
SET @Seconds = (@Seconds % 60) SELECT @TotalTime = ‘The Time Difference from today for this call is ‘+ CAST(ABS(@Hours) AS Varchar(2)) + ‘ hours ‘ + CAST(ABS(@Minutes) AS Varchar(2)) + ‘ minutes ‘ + CAST(ABS(@Seconds) AS Varchar(2)) + ‘ seconds ‘ + ‘on ‘ + CAST(@DateRan AS Varchar(30)) + ‘ for the call that was placed on ‘ + CAST(@DateRan AS Varchar(30)) my apologies about all this as i am new to SQL ta tymbuktu

Hi, seems that it asks you to declare the @appid, where is this variable declare? AKTHAR DILMOHAMUD
65 BENARES ST
PORT LOUIS
MAURITIUS

It is because you did not declare @appid
Can you explain what you are trying to do? Madhivanan Failing to plan is Planning to fail

HI and firstly thankyou for both your replies… basically i am trying to determine the time between the time now() and the time that a record was entered into the system (datetimeofcall) initially i was attempting it on the asp page <%
Dim ShortYearsDifference ‘ calculates the clients age and inserts into a hidden field in page for update of record
DaysDifference = DateDiff("d", rsHelpdesk("datetimeofcall"), Now() )
YearsDifference = DaysDifference / 365
timeDifference = DaysDifference / 24
ShortYearsDifference = Left(YearsDifference,2)
%>
<%=DaysDifference%>
BUT i was told to do it on the Database end so i am trying it on the DB the ASP version i did i can only get the number of DAYS but not the exact day, time seconds etc etc… Any help will be greatful. regards Musa

Is this your complete statement or just a part thereof? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Select DateDiff(hh,Col,Getdate()) from yourTable
See DateDiff function in BOL Madhivanan Failing to plan is Planning to fail

HI the asp script is the complete statement . Sorry Madhivanan i am sorta lost here… am i taking the ASP approach or the SQL DB approach.. therefore where do i add the select criteria you specified. Regards Musa

Hi Ideally i would like ti to diplay the value on the ASP page in hour format?

Musa, if you used your dB APPROACH, it sould work just try to define the @appid variable. Is it a user input??? AKTHAR DILMOHAMUD
65 BENARES ST
PORT LOUIS
MAURITIUS

Sounds like a presentational issue to me. I wouldn’t do this in the database. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Yeah Br.Aktar and Frank i tried doing it on the page itself buti got values such as this if i was finding out the hours between now and the time in the DB 10.2916666666667 (this was for the value 09/01/2005 15:41:39 in the datetimeofcall field.) this is not right.. is there any thing you can advise me/// Yeah Br.Aktar i did define the variable @appID this a int that is automatically generated within the db as a new record is created via the asp page. Any help will be much apprieciated.

Hi ,
see if this example help
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO taken from BOL AKTHAR DILMOHAMUD
65 BENARES ST
PORT LOUIS
MAURITIUS

see also this article
hope it helps
http://www.databasejournal.com/features/mssql/article.php/3076421 AKTHAR DILMOHAMUD
65 BENARES ST
PORT LOUIS
MAURITIUS

Now you’ve got me confused. You want to find out the time difference between now and some column in your table? See if this helps you:
DECLARE @dt DATETIME, @dt_Start DATETIME
SELECT @dt = GETDATE(), @dt_Start = ‘20050913’ SELECT
@dt Now
, DATEDIFF(second,@dt_Start, @dt) / 3600 Hours
, DATEDIFF(second,@dt_Start, @dt)/ 60 % 60 Minutes
, (DATEDIFF(second,@dt_Start, @dt) % 60) Minutes
Now Hours Minutes Minutes
—————————————————— ———– ———– ———–
2005-09-13 14:39:17.230 14 39 17 (1 row(s) affected) To make it easier to check, I’ve set the start date to midnight of today. Is this going your direction? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Akthar i have tried this… USE helpdesk
GO
SELECT DATEDIFF(hour, DateTimeOfCall , getdate()) AS appid2
FROM application_tbl
GO but i get the values in SQl Analyser.. it does not put it into the actual field appID2 so that i can pull it out on the ASP page… i just wana real time value of the the columns… so that its viewable on the ASP page.. do you think i need to do a storeprocedure or something…..

Frank thanks for ur effort, i really am sorry.. i cant get that too work.. all i want is the difference bettern the current date/time and the date/time in the database field… (called DateTimeOfCall ) and for it to be displayed in HOURS on a web page… i am using ASP. Its just that this bloke on the web told me to do the whole thing within SQL DB but i dont see how that can work and be shown on the ASP page… ta

Okay, last try before I go home for today [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <pre id="code"> DECLARE @dt DATETIME, @dt_Start DATETIME SELECT @dt = GETDATE(), @dt_Start = ‘20050913’ DECLARE @seconds INT DECLARE @hours INT DECLARE @minutes INT – SET @seconds = DATEDIFF(second,@dt_Start, @dt) SET @hours = FLOOR(@seconds/(60*60)) SET @minutes = FLOOR((@seconds – (@hours*3600))/60) SET @seconds = FLOOR(@seconds %60) SELECT @dt SELECT CAST(@hours AS VARCHAR(5))+ ’:’ + RIGHT(‘0′ + CONVERT(VARCHAR(2), @minutes), 2)+ ’:’ + RIGHT(‘0’ + CONVERT(VARCHAR(2), @seconds), 2) AS Time_Elapsed —————————————————— 2005-09-13 16:18:13.760 (1 row(s) affected) Time_Elapsed ———— 16:18:13 (1 row(s) affected) </pre id="code"> – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Frank i get this mate… Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘@dt’.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘@hours’.
no worries will speak to ou 2morw ta

Yes, probably we have more luck tomorrow. Though I’m still uncertain how your result should look like. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

hi, you managed to do it tymbuktu? AKTHAR DILMOHAMUD
65 BENARES ST
PORT LOUIS
MAURITIUS

]]>