Datetime Diff Query Syntax
Apr 5, 2006
Hi.
I'm trying but not getting correct results.
I have two tables
one with app, msg, time
(varchar,datetime,varchar)
app1 start 2006-04-03 13:33:36.000
app1 stuff 2006-04-03 13:33:36.000
app1 end 2006-04-03 13:33:36.000
app1 start 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app2 stuff 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app3 start 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app3 end 2006-04-03 13:33:36.000
app1 end 2006-04-03 13:33:36.000
and another with dr watson crash info
(varchar, datetime)
app1 2006-04-03 13:33:36.000
app2 2006-04-03 13:33:36.000
app1 2006-04-03 13:33:36.000
app1 2006-04-03 13:33:36.000
app3 2006-04-03 13:33:36.000
I'm trying to make a query that will allow
me to see what entries in the first table
occurred wtihin, say, a minute, or maybe 40
seconds of any of the entries in the second
table.
I want all the entries in the second table to
be present, so I know it has to be some sort
of join, probably an outer join.
my syntax is giving me bad results, probably
because I'm just out of practice.
can someone tell me how to put a query together
so I see the data I'm looking for?
Thanks
Jeff
Jeff Kish
View 3 Replies
ADVERTISEMENT
May 28, 2008
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007
but in query analizer I get the result of
12/31/2006
Why the different dates
View 4 Replies
View Related
Feb 12, 1999
I need to write a 'select' statement to fetch data from different tables, which are located on different servers.
Can any one help in writing this 'select' statement with out moving the tables on to same server.
Thanks in Advance.
Murali Raparla.
View 2 Replies
View Related
Sep 11, 2007
My goal is to add a diff into a query that grabs data from 2 different tables.
The code:
SELECT
MIN(TableName) as TableName,
ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8
,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16,
COL17, COL18, COL19, COL20, COL21
FROM
(
SELECT 'Table A' as TableName,
SessionID as ID1,
StartDateCode as COL1,
StartTimeCode as COL2,
EndDateCode as COL3,
EndTimeCode as COL4,
HandledByCode as COL5,
DispositionCode as COL6,
DNISCode as COL7,
CallServiceQueueCode as COL8,
ApplicationCode as COL9,
IVREndPointCode as COL10,
BankCode as COL11,
TotalQueueTimeSS as COL12,
TotalAgentTalkTimeSS as COL13,
TotalAgentHoldTimeSS as COL14,
TotalAgentHandleTimeSS as COL15,
--TotalIVRTimeSS as COL16,
AfterHoursFlag as COL17,
SourceSystemID as COL18,
anubisTransferExtNumber as COL19,
anubisEndPoint as COL20,
AccountNumber as COL21
from [pdx0sql45].Rubicon_Marts.dbo.INB_Call_Fact
where startdatecode between 2738 and 2769
UNION all
SELECT 'Table B' as TableName,
SessionID as ID1,
StartDateCode as COL1,
StartTimeCode as COL2,
EndDateCode as COL3,
EndTimeCode as COL4,
HandledByCode as COL5,
DispositionCode as COL6,
DNISCode as COL7,
CallServiceQueueCode as COL8,
ApplicationCode as COL9,
IVREndPointCode as COL10,
BankCode as COL11,
TotalQueueTimeSS as COL12,
TotalAgentTalkTimeSS as COL13,
TotalAgentHoldTimeSS as COL14,
TotalAgentHandleTimeSS as COL15,
--TotalIVRTimeSS as COL16,
AfterHoursFlag as COL17,
SourceSystemID as COL18,
anubisTransferExtNumber as COL19,
anubisEndPoint as COL20,
AccountNumber as COL21
from pdx0sql04.Rubicon_Marts.dbo.INB_Call_Fact
where startdatecode between 2738 and 2769
) tmp
GROUP BY ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8
,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16,
COL17, COL18, COL19, COL20, COL21
HAVING COUNT(*) = 1
ORDER BY 2,1
---------
Is it possible to add a command into the query to output diff/compare scenario?
Thanks in advance for any help.
View 3 Replies
View Related
Apr 25, 2007
Hi @all,
I designed a query in report designer, data tab. This query runs 24 secs, when I execute it from the data tab in report designer.
Publishing the report and running it from the report server. The query needs 40x the time as in report designer.
Looking at the Execution Log, the report needs 800 secs for TimeDataRetrieval.
Why is it so much slower to run the query from report server? The report designer has also to retrieve the data, and runs so much faster.
Thanks for any hints
T
View 2 Replies
View Related
Nov 11, 2002
Hi,
select first_name,last_name, order_date
from orders where convert (datetime, order_date) > '11/08/02'
I get the following error while executing this on sql server 2000.
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
This statement wokrs with 7.0. Also I haven't come across anything that tells me that this syntax is not supported in sql server 2000.
Then why am I getting this error? Is there any database setting that needs to be changed? How can I make this statement work in 2000?
Thanks,
Shaili
View 3 Replies
View Related
Sep 25, 2014
I have two tables table1 and table2 and having a common column "col1"
When i ran the following query
UPDATE table1, table2 SET col1=FALSE WHERE id = 1;
getting the following error
Error Code: 1052
Column 'col1' in field list is ambiguous
id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.
View 3 Replies
View Related
Jul 20, 2005
Hi group,I have a select statement that if run against a 1 million recorddatabase directly in query analyzer takes less than 1 second.However, if I execute the select statement in a stored procedureinstead, calling the stored proc from query analyzer, then it takes12-17 seconds.Here is what I execute in Query Analyzer when bypassing the storedprocedure:USE VerizonGODECLARE @phonenumber varchar(15)SELECT @phonenumber = '6317898493'SELECT Source_Identifier,BADD_Sequence_Number,Record_Type,BAID ,Social_Security_Number ,Billing_Name,Billing_Address_1,Billing_Address_2,Billing_Address_3,Billing_Address_4,Service_Connection_Date,Disconnect_Date,Date_Final_Bill,Behavior_Score,Account_Group,Diconnect_Reason,Treatment_History,Perm_Temp,Balance_Due,Regulated_Balance_Due,Toll_Balance_Due,Deregulated_Balance_Due,Directory_Balance_Due,Other_Category_BalanceFROM BadDebtWHERE (Telephone_Number = @phonenumber) OR (Telephone_Number_Redef =@phonenumber)order by Service_Connection_Date descRETURNGOHere is what I execute in Query Analyzer when calling the storedprocedure:DECLARE @phonenumber varchar(15)SELECT @phonenumber = '6317898493'EXEC Verizon.dbo.baddebt_phonelookup @phonenumberHere is the script that created the stored procedure itself:CREATE PROCEDURE dbo.baddebt_phonelookup @phonenumber varchar(15)ASSELECT Source_Identifier,BADD_Sequence_Number,Record_Type,BAID ,Social_Security_Number ,Billing_Name,Billing_Address_1,Billing_Address_2,Billing_Address_3,Billing_Address_4,Service_Connection_Date,Disconnect_Date,Date_Final_Bill,Behavior_Score,Account_Group,Diconnect_Reason,Treatment_History,Perm_Temp,Balance_Due,Regulated_Balance_Due,Toll_Balance_Due,Deregulated_Balance_Due,Directory_Balance_Due,Other_Category_BalanceFROM BadDebtWHERE (Telephone_Number = @phonenumber) OR (Telephone_Number_Redef =@phonenumber)order by Service_Connection_Date descRETURNGOUsing SQL Profiler, I also have the execution trees for each of thesetwo different ways of running the same query.Here is the Execution tree when running the whole query in theanalyzer, bypassing the stored procedure:--------------------------------------Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))|--Bookmark Lookup(BOOKMARK:([Bmk1000]),OBJECT:([Verizon].[dbo].[BadDebt]))|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))|--Concatenation|--IndexSeek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Index]),SEEK:([BadDebt].[Telephone_Number]=[@phonenumber]) ORDERED FORWARD)|--IndexSeek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Redef_Index]),SEEK:([BadDebt].[Telephone_Number_Redef]=[@phonenumber]) ORDEREDFORWARD)--------------------------------------Finally, here is the execution tree when calling the stored procedure:--------------------------------------Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@phonenumber] OR[BadDebt].[Telephone_Number_Redef]=[@phonenumber]))|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),1, 10)))|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))--------------------------------------Thanks for any help on my path to optimizing this query for ourproduction environment.Regards,Warren WrightScorex Development Team
View 5 Replies
View Related
Jun 4, 2007
Probably one of the easiest queries some have seen around, but good ol' MS and their dates. I can't get around this error on this simple query. It will be a job that will run probably several times a day. I haven't even gotten passed to the second part of it....I've also seen this error is a VERY popular error, even with advanced programmers it seems. WOW. Basically, I want to keep the row count to 2 days. Well, if you have any suggestions, I'd take 'em. Thanks!
Delete from tblCallIndexes where inum in
(select inum from tblCalls where StartedAt < 'DATEADD(d,-2,GETDATE())')
View 14 Replies
View Related
Aug 6, 2004
Here's the deal. I'm a newbie trying to update a date (@Effective) in sql server. I've been trying all sorts of things, but nothing seems to work. Here's my current code:
Sub btnUpdate_Click(sender As Object, e As EventArgs)
Dim sqlStmt As String
Dim conString As String
Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim sqldatenull As SqlDateTime
Try
sqlStmt = "UPDATE [Submission] SET [Effective]=Convert(DateTime, '@Effective', 101), "& _
"[Status]=@Status WHERE ([Submission].[Submission]=@Submission)"
conString = "server='(local)'; user id='sa'; password='password'; database='database'"
cn = New SqlConnection(conString)
cmd = New SqlCommand(sqlStmt, cn)
cmd.Parameters.Add(New SqlParameter("@Submission", SqlDbType.Int, 4))
cmd.Parameters.Add(New SqlParameter("@Effective", SqlDbType.VarChar, 10))
cmd.Parameters.Add(New SqlParameter("@Status", SqlDbType.VarChar, 8))
sqldatenull = SqlDateTime.Null
If (txtEffective.Text = "") Then
cmd.Parameters("@Effective").Value = sqldatenull
Else
cmd.Parameters("@Effective").Value = txtEffective.Text
End If
cmd.Parameters("@Status").Value = dropStatus.SelectedItem.Text
cmd.Parameters("@Submission").Value = LabelSubmission.Text
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
Response.Redirect("main.aspx")
Catch ex As Exception
lblError.Text = ex.Message
Finally
cn.Close()
End Try
End Sub
My db is setup as follows:
Submissionint4
Insuredvarchar30
Statevarchar2
Effective datetime 8
Underwritervarchar9
Statusvarchar8
Cleareddatetime 9
I'm losing my hair by the fist full... Somebody please stop the insanity!
View 2 Replies
View Related
Apr 20, 2005
Somewhere, either in the
aspx.vb code, or the stored procedure that's being called, it doesn't
like my code since I am getting the dreaded "syntax error converting
datetime from character string" error. I've been battling this
for two days - can someone spot what is wrong here?
The dtWdate variable is coming from a calendar control on my aspx page.
Please help - I've tried every conceivable way I can think to do this without success.
Lynnette
Here's the code:
Private Sub GetFLSAEmpInfoRecs()
'uses the AppSettings table to generate WHERE clause from the filter values
Dim dtWdate As DateTime = Calendar1.SelectedDate
Dim wValue As String = Session("svFilterValue")
Dim cnn As New SqlConnection(constants.SQLConStrFLSA)
Dim cmd As New SqlCommand("usp_AddFLSA", cnn)
cmd.CommandType = CommandType.StoredProcedure
cnn.Open()
cmd.Parameters.Add(New
SqlParameter("@dtWDate", SqlDbType.DateTime)).Value =
Convert.ToDateTime(dtWdate)
cmd.Parameters.Add(New
SqlParameter("@whereString", SqlDbType.VarChar, 500)).Value = wValue
cmd.ExecuteNonQuery()
cnn.Close()
End Sub
And here is the stored procedure:
ALTER PROC usp_AddFLSA
@dtWdate datetime,
@whereString as varchar(255)
AS
DECLARE @strSQL as varchar(2000)
SET @strSQL =
'
INSERT INTO FLSAEmpInfo
( Emp_Number,
PT_ID,
Emp_Division,
Emp_DeptInfo,
Emp_Supervisor,
Emp_Location,
Emp_Union,
Emp_SG,
Emp_Shift,
WrkDate
)
SELECT
hcsoSharedTables.dbo.Employee2.Emp_Number,
hcsoSharedTables.dbo.Employee2.[ID],
hcsoSharedTables.dbo.Employee2.Division,
dbo.udf_getDeptInfo(hcsoSharedTables.dbo.employee2.Department,hcsosharedtables.dbo.employee2.Dept_Mgr,hcsosharedtables.dbo.employee2.job_dept_code),
hcsoSharedTables.dbo.Employee2.Job_Supervisor,
hcsoSharedTables.dbo.Employee2.Loc_Name,
hcsoSharedTables.dbo.Employee2.[Union],
hcsoSharedTables.dbo.Employee2.Sched_Group,
hcsoSharedTables.dbo.Employee2.Shift,' +
@dtWdate + ' ' + 'FROM hcsoSharedTables.dbo.Employee2' + @whereString
EXEC(@strSQL)
View 4 Replies
View Related
Nov 14, 2005
Hi all, The last week this was working, and today i was testing and all start with errors. This is my SQL sentence:command = New SqlCommand("update ctlg_users set u_last_access = '" & FormatDateTime(Now(), 1) & "' where u_id = " & cstr(l_u_id),oConn)Is there an error? why was before working and now don`t?, including an old ASP page that i´m still using fall back to the same error when is using FormatDateTime, í´ve tried in the ASPX page using datetime.now.tostring("dd/mm/yyyy") but i got an error too!! HEEEELP!Thanks :D
View 2 Replies
View Related
Nov 24, 2005
Hi,
I'm having a strange problem with the following stored proc:
Code:
sql
declare @orderBy tinyint
set @orderBy = 2
SELECT tblUsers.userID, firstName, lastName, dateRegistered, count(movieSessionID) As sessionsCount FROM tblUsers
LEFT JOIN tblMovieSessions on tblUsers.userID = tblmovieSessions.userID
Group By tblUsers.userID, firstName, lastName, dateRegistered
Order By Case
WHEN @orderBy = 2 THEN firstName
WHEN @orderBy = 3 THEN dateRegistered
WHEN @orderBy = 4 THEN count(movieSessionID)
else lastName
End desc
When I execute it with @orderBy = 1 or 2 I get the following error message: Quote: Server: Msg 241, Level 16, State 1, Line 5
Syntax error converting datetime from character string.
However, with @orderBy = 3 or 4 it executes successfully.
I don't understand why this change should have this impact because the error seems to be occuring on the first line (i.e. SELECT...).
If I take the dateRegistered column out of the query I get a new error message:
Quote:
Server: Msg 245, Level 16, State 1, Line 5
Syntax error converting the nvarchar value 'Adam' to a column of data type int.
I don't understand why it is trying to convert these columns during the query anyway, let alone how to solve the problem.
Anyone got any ideas?
View 1 Replies
View Related
Sep 29, 2005
I am using INSERT to replace about 160 fields for one company in the company table, all goes well until I come to the Rn_edit_date field which is a datetime datatype. When I insert the value of:
cast('2005-03-15 09:25:44.463' as datetime)
Error message as a result is: Syntax error converting datetime from character string.
NOTE: 2005-03-15 09:25:44.463 is the correct format which all other companies abide by, so I'm totally lost at where I'm going wrong?
Any help would be appreciated
View 5 Replies
View Related
Jun 22, 2007
I am having a problem on running the following queiry
select * from my_table where unitclass_id=247
and Year(cast (column1 as datetime))=Year(dateadd(wk,0,getdate()) )
column1 type nvarchar(10) and stores date in format like 2007-06-22.
I am trying to select all records for the current year from my_table and getting an error
when running that queiry
Syntax error converting datetime from character string.
Please help
Thank you
View 8 Replies
View Related
Mar 6, 2008
I have a field that has dates that are currently set as a string. Dates are as follows:
Apr 01 2006
Aug 20 2006
Aug 20 2006
I did an isdate() function on the field to see if it can be converted and it came back as true; however, I have attempted several different ways to convert the field to a date field and it continues to give me a syntax error. Any assistance would be great!!!
I tried:
convert(datetime,cast(New_Date as varchar),100)
convert(varchar(25),New_Date,107)
Both gave me syntax errors.
View 10 Replies
View Related
Nov 14, 2007
When I convert this data into a datetime, i get the foll error.
Syntax error converting datetime from character string.
SELECT convert(datetime,'09/2007')
Basically I have a field in a tbl that has varchar datatype, and is in this format of mm-yyyy. I need to compare this value to current date and so I convert it to datetime. but it errs
Pl help
View 3 Replies
View Related
Nov 14, 2007
Hi,
I have Table A . we already have 80 columns . we have to add 65 more columns.
we are populating this table from oracle .and we need to populate those 65 columns again from the same table.
Is it a better idea to add those new 65 columns to the same table or new table.
If we go for the same table then loading time will be double, If I go for new table and If i am able to run both the packages which loads table data from same oracle server to difffrent sql tables then we should be good. But if we run in to temp space issues on oracle server . Then i have to load the two tables separately which consumes the same time as earlier one.
I was thinking if there is a way in SSIS where I can pull data from same oracle table in to two diff sql tables at same time?
View 1 Replies
View Related
Mar 17, 2007
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
View 2 Replies
View Related
Jul 20, 2005
CREATE PROCEDURE dbo.Synchronization_GetNewRecords(@item varchar(50),@last datetime)ASSET NOCOUNT ONDECLARE @sql nvarchar(4000)SET @sql = 'SELECT * FROM ' + @item + ' WHERE LastUpdated >' + @lastEXEC sp_executesql @sql, N'@Type varchar(50), @Last datetime', @item, @lastThis is my SP. Very simple. But it is throwing the error in the subject line.Any help would be greatly appreciated.
View 1 Replies
View Related
May 22, 2008
i have a stored proc running against the Northwind DB
Code Snippet
CREATE PROCEDURE CustomPaging
@intOrderID varchar(10),
@strCustomerID varchar(10),
@intEmployeeID int,
@dteOrderDate datetime,
@startRowIndex int,
@maximumRows int,
@SortOrder int
AS
DECLARE @TempItems TABLE
(
ID int IDENTITY,
OrderID int
)
DECLARE @maxRow int
SET @maxRow = (@startRowIndex + @maximumRows) - 1
SET ROWCOUNT @maxRow
INSERT INTO @TempItems (OrderID)
SELECT OrderID
FROM Orders o
INNER JOIN Customers c ON c.CustomerID = o.CustomerID
INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID
ORDER BY CASE @SortOrder WHEN 0 THEN OrderID
WHEN 1 THEN c.CompanyName
WHEN 2 THEN e.FirstName
WHEN 3 THEN o.OrderDate
WHEN 4 THEN o.RequiredDate
WHEN 5 THEN o.ShippedDate END
SET ROWCOUNT @maximumRows
SELECT o.OrderID, CompanyName, e.FirstName + ' ' + e.LastName EmployeeName, o.OrderDate, o.RequiredDate, o.ShippedDate
FROM @TempItems t
INNER JOIN Orders o ON o.OrderID = t.OrderID
INNER JOIN Customers c ON c.CustomerID = o.CustomerID
INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID
WHERE
t.[ID] >= @startRowIndex
AND
(o.OrderID LIKE @intOrderID OR @intOrderID = '')
AND
(o.CustomerID = @strCustomerID OR @strCustomerID='')
AND
(o.EmployeeID = @intEmployeeID OR @intEmployeeID = 0)
AND
(o.OrderDate = @dteOrderDate OR @dteOrderDate IS NULL)
SET ROWCOUNT 0
GO
when i execute the sp with @sortOrder = 0, it works fine
exec CustomPaging '','',0,null, 1, 10, 0
but if i try anything else (@SortOrder=1) i get the error message
exec CustomPaging '','',0,null, 1, 10, 1
Syntax error converting datetime from character string.
but im unsure what is causing this problem? and how to sort it?
Any help is greatly appreciated.
Cheers.
Craig
View 13 Replies
View Related
Jan 13, 2006
Hello,I've been searching the web for quite some time to resolve the problemof "1/1/1900" returning in a datetime field in SQL that resulted from ablank (not NULL) value being passed to it through an ASP page.The solution is that a NULL value needs to passed to SQL from ASP.Thats fine...I understand the why the problem is happening and thesolution around it. HOWEVER, I can't seem to get the proper syntax towork in the ASP page. It seems no matter what I try the "1/1/1900"still results. Below are a few variations of the code that I havetried, with the key part being the first section. Does anyone have anysuggestions?!?!?______________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed") <> "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = NULLend ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_______________Thanks in advance!!!!
View 7 Replies
View Related
Jul 12, 2007
Hi All,
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Thanks in advance,
Anand
View 4 Replies
View Related
Jan 7, 2004
Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:
CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime
as
set dateformat dmy
go
declare @startdate as varchar
declare @enddate as varchar
--print "query aan het uitvoeren"
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@mID' and njdate between '@startdate' and '@enddate'
GO
Thanx in front
Cya
View 14 Replies
View Related
Nov 30, 1998
I recently wrote a query similar to this:
Select e.name, e.employee_id, d.department_name
from employees e,departments d
where e.department_id = d.department_id
An associate recommended that I change this query to add the words INNER JOIN like this:
Select e.name, e.employee_id, d.department_name
from employees e inner join departments d
on e.department_id = d.department_id
I was of the understanding that the first query was Microsoft SQL Syntax, and that the second example was ANSI SQL syntax, but that they executed on the server the same way. My associate however thinks that the second example will execute faster.
Would anyone like to confirm this?
View 2 Replies
View Related
Aug 29, 2007
Hi.. I want to join this query
Code:
select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & "
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0
in this query
Code:
select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D,
iCalls_Status.Status_Label from
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on
iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID
The Place where i need to Join is after
Code:
iCalls_Status.Status_ID,iCalls_Status.Status_Label
and before
Code:
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID )
I want to add ( / ) in between these 2 queries. The reason is for example first query will return '5' and second '10' , so the output i need is 5 / 10. And i need to put this query in a variable (Countrec) like
Code:
select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ( / )
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec
The Final Query would be something like this
Code:
select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D,
iCalls_Status.Status_Label, select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ( / )
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec from
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on
iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID
but this syntax is not correct..Please can U get me the Correct Syntax.
View 7 Replies
View Related
Jan 13, 2005
I have a query that I am running out of sql server 2000 that is pulling duplicate records. I can probably figure it out but I am wondering if someone could look at it and point out errors in my syntax and/or structure.
Thanks!
code:--------------------------------------------------------------------------------
SELECT p.ParticipantID, pr.RaceID, p.FirstName, p.LastName, pr.Bib, p.Gender, pr.Age,
pr.AgeGrp, p.DOB, p.Address, p.City, p.St, p.Zip, pr.Clyde, pr.WhlChr, pr.RcWlk,
p.Phone, p.Email, reg.ShrtSize, reg.ShrtStyle, reg.WhereReg, reg.DateReg, reg.AmtPd
FROM Participant p INNER JOIN PartReg reg ON p.ParticipantID = reg.ParticipantID JOIN PartRace pr
ON pr.ParticipantID = p.ParticipantID JOIN RaceData rd ON pr.RaceID = rd.RaceID
WHERE (rd.EventID = 45 AND pr.RaceID = reg.RaceID) ORDER BY p.LastName
--------------------------------------------------------------------------------
View 1 Replies
View Related
Jan 13, 2005
I have a query that I am running out of sql server 2000 that is pulling duplicate records. I can probably figure it out but I am wondering if someone could look at it and point out errors in my syntax and/or structure.
Thanks!
code:--------------------------------------------------------------------------------
SELECT p.ParticipantID, pr.RaceID, p.FirstName, p.LastName, pr.Bib, p.Gender, pr.Age,
pr.AgeGrp, p.DOB, p.Address, p.City, p.St, p.Zip, pr.Clyde, pr.WhlChr, pr.RcWlk,
p.Phone, p.Email, reg.ShrtSize, reg.ShrtStyle, reg.WhereReg, reg.DateReg, reg.AmtPd
FROM Participant p INNER JOIN PartReg reg ON p.ParticipantID = reg.ParticipantID JOIN PartRace pr
ON pr.ParticipantID = p.ParticipantID JOIN RaceData rd ON pr.RaceID = rd.RaceID
WHERE (rd.EventID = 45 AND pr.RaceID = reg.RaceID) ORDER BY p.LastName
--------------------------------------------------------------------------------
View 7 Replies
View Related
Feb 6, 2008
am looking to make a select query that has some condition where it should look like:
select if(SomeField is null) then 'Not Set' else SomeField
from SomeTable
how it can be done ?????
View 9 Replies
View Related
Nov 15, 2007
Hi guys<
I wan to know what command can I use to view the latest date of posting of interest in my database given the following tables
Table: DepositInterest
Fields: code1, code2, interestdate
code 1 and 2 are primary keys.
Then I tried to run a query using where clause but then it happened that all interestdate are viewed.
What I want is to just view the latest interestdate.
View 4 Replies
View Related
Jul 23, 2005
Hi all,I have the following tables:T1==ID Name-- ----1 Name12 Name2T2==ID Color-- ----1 Color11 Color22 Color22 Color3and I would like to get the following output (without duplicates):ID Colors-- ------1 Color1, Color22 Color2, Color3Is it possible?
View 4 Replies
View Related
Sep 17, 2005
Hello All,I have the following table:CREATE TABLE [dbo].[TBL_NAME] ([NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[STANDARD_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL) ON [PRIMARY]GOWith values:insert into tbl_namevalues('DAN', 'DANIEL')insert into tbl_namevalues('DANNY', 'DANIEL')insert into tbl_namevalues('DANYY', 'DANIEL')Question is:I need want to construct a query which returns all names for a standardname plus the standard name itself.e.g.if name = 'DAN' then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'ff name = 'DANIEL', then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'i have the following sql:declare @name varchar(50)select @name = 'DANIEL'select standard_name from tbl_name where name = @nameunionselect name from tbl_name where standard_name = (select standard_namefrom tbl_name where name = @name)unionselect name from tbl_name where standard_name = @nameunionselect standard_name from tbl_name where standard_name = @name----declare @name varchar(50)select @name = 'DANNY'select standard_name from tbl_name where name = @nameunionselect name from tbl_name where standard_name = (select standard_namefrom tbl_name where name = @name)unionselect name from tbl_name where standard_name = @nameunionselect standard_name from tbl_name where standard_name = @name----Both appear to work fine..can anyone see a fault or suggest a cleanerway to achieve the above ?Suggestions/pointers appreciatedThanks in advance
View 3 Replies
View Related
Jan 20, 2007
sql query syntax?i write this query but error get : column not foundhow can i write ?SELECT c.stock_cat_name, a.stock_code, c.description, a.date,IIF(MID(a.type,2,1)="I",SUM(a.quantity),0) AS iqty,IIF(MID(a.type,2,1)="O",SUM(a.quantity),0) AS oqtyFROM stock_tran AS a LEFT JOIN stock AS c ON c.stock_code=a.stock_codeWHERE a.date>=[from_date] And a.date<=[to_date]GROUP BY c.stock_cat_name, a.stock_code, c.description, a.date,MID(a.type,2,1)ORDER BY c.stock_cat_name, a.stock_code, a.date, MID(a.type,2,1);this database driver sage line 50 odbci write a simpale query of this database, successful run but when iwrite in condition (IF) is not run error comes : column not found.rashesh
View 2 Replies
View Related
