SEARCH YOUR SOLUTION HERE  

Syntax error converting datetime from character string

hi

I'm having problem with sql query

i'll explain u what i'm trying to do
when a user sends enquiry for a product it goes to table propertyhittable where hitid the index. if the receiver replies to the enquiry, his reply goes to table mailbox and all subsequent mail correspondence goes to mailbox. now on my Inbox Page i have to dispaly all the enquiries like gmail (each mail clustered with all past correspondence)

I used the following query:

select HitId,hitid as mailid,Login_Id,Star,Subject,Prop_Code,visitdate, replydate,mailread from propertyhittable where login_id'71727' and deleted=0 and prop_code in (select prop_code from resi_sell where login_id='71727' union select prop_code from comm_sell where login_id='71727' union select prop_code from land_sell where login_id='71727') union (select mb1.Login_Id,mb1.Star,mb1.Subject,mb1.Prop_Code,mb 1.maildate as visitdate, mb1.maildate as replydate,mb1.mailread,mb2.hitid,mb2.mailid from (select hitid,max(mailid) as mailid from mailbox group by hitid) as mb2 inner join mailbox as mb1 on mb1.hitid = mb2.hitid and mb2.mailid = mb1.mailid where login_id71727 and deleted=0 and mb2.hitid in (select hitid from propertyhittable where login_id='71727') ) order by replydate desc

i got
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Posted On: Monday 26th of November 2012 01:40:41 AM Total Views:  311
View Complete with Replies




Related Messages:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'   (299 Views)
I received an error message that I can't seem to resolve. The following: Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Text Driver] Query must have at least one destination field. /process1.asp, line 16 Any assistance with the above will be greatly appreciated.
Incorrect Syntax error   (247 Views)
I amgetting this error in SQL. Error in SELECT clause: expression near '='. Missing FROM clause. Missing or incomplete SELECT clause. Unable to parse query text. Code: SELECT dbo.TestResult.RunId, dbo.TestResult.TestName AS Project, dbo.TestResult.IsRunnable, dbo.TestResult.Owner, dbo.TestResult.CategoryFullName, dbo.TestResult.CategoryId, dbo.TestResult.StartTime, dbo.TestResult.EndTime, dbo.TestResult.SubTestCount, dbo.TestResult.SubTestPass, dbo.TestRun.Name, dbo.TestRun.FileStorage, dbo.TestRun.TestsTotal, dbo.TestRun.TestsExecuted, dbo.TestRun.TestsPassed, dbo.TestRun.TestsCompleted, dbo.TestRun.TestsFailed, dbo.TestRun.TestsNotRun, dbo.TestRun.TestsInconclusive, dbo.TestRun.TestsAborted, dbo.TestRun.RunUser, dbo.TestRun.PublishUser, dbo.TestRun.DateCreated, dbo.TestRun.DateQueued, dbo.TestRun.DateStarted, dbo.TestRun.DateFinished, dbo.TestRun.RunHasCoverage, dbo.TestRun.RunPassed, dbo.TestRun.BuildUri, dbo.TestRun.TeamProject, NSEQAEngine.dbo.TestProducts.Product AS Product, NSEQAEngine.dbo.TestProducts.Script AS Script, NSEQAEngine.dbo.TestProducts.Component, NSEQAEngine.dbo.TestProducts.Description, NSEQAEngine.dbo.TestProducts.Responsible, NSEQAEngine.dbo.TestProducts.ResponsibleEmail, NSEQAEngine.dbo.TestProducts.Manual, NSEQAEngine.dbo.TestProducts.Comment, dbo.TestResult.TestOutcome As Status = CASE WHEN dbo.TestResult.TestOutcome = 'Completed' THEN 'Passed' WHEN dbo.TestResult.TestOutcome = 'Failed' THEN 'Failed' WHEN dbo.TestResult.TestOutcome = 'Incomplete' THEN 'Debatable' WHEN dbo.TestResult.TestOutcome Is null THEN 'Debatable' END, dbo.TestResult.TestType = CASE WHEN dbo.TestResult.Testtype = 'Unknown' THEN 'TestComplete' WHEN dbo.TestResult.Testtype = 'other' THEN 'NSE QA Portal Test Type' WHEN dbo.TestResult.Testtype Is null THEN 'Invalid' END, FROM dbo.TestResult INNER JOIN dbo.TestRun ON dbo.TestResult.RunId = dbo.TestRun.RunId LEFT OUTER JOIN NSEQAEngine.dbo.TestProducts ON dbo.TestResult.RunId = NSEQAEngine.dbo.TestProducts.RunID WHERE (NSEQAEngine.dbo.TestProducts.Component '')
Incorrect syntax near 'GO'.   (153 Views)
my query: Code: 'create contents table sqlContents = "USE [Shem]" sqlContents = sqlContents & "GO" sqlContents = sqlContents & "/****** Object: Table [dbo].[contents] Script Date: 03/13/2007 15:27:58 ******/" sqlContents = sqlContents & "SET ANSI_NULLS ON" sqlContents = sqlContents & "GO" sqlContents = sqlContents & "SET QUOTED_IDENTIFIER ON" sqlContents = sqlContents & "GO" sqlContents = sqlContents & "SET ANSI_PADDING ON" sqlContents = sqlContents & "GO" sqlContents = sqlContents & "CREATE TABLE [dbo].[contents](" sqlContents = sqlContents & " [idkey] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL," sqlContents = sqlContents & " [contents] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL," sqlContents = sqlContents & " [tags] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL," sqlContents = sqlContents & " [userID] [int] NULL," sqlContents = sqlContents & " [hiddenTags] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL," sqlContents = sqlContents & " [title] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL," sqlContents = sqlContents & " [theDate] [datetime] NULL," sqlContents = sqlContents & " [timekey] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL," sqlContents = sqlContents & "CONSTRAINT [PK_contents] PRIMARY KEY CLUSTERED " sqlContents = sqlContents & "(" sqlContents = sqlContents & " [idkey] ASC" sqlContents = sqlContents & ")WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]" sqlContents = sqlContents & ") ON [PRIMARY]" sqlContents = sqlContents & "GO" sqlContents = sqlContents & "SET ANSI_PADDING OFF" unfortunately I don't know which GO
How insert string into dtb   (224 Views)
I use this statement to insert string value in my test dtb is it correct Code: strSql = "UPDATE guestbook SET" _ & " isapproved = '1'" _ & " WHERE gid =" & arr_idcount(c)
UPDATE: worked around the code = 3001 error   (211 Views)
I replaced the recordset command line with adCmdTableDirect and it fixed that error right up. I have another error now.. I'm a SQL noob any help is appriciated. -- Database error Procedure = usp_Childs_Name The database could not retrieve the Birth record(s) because of the following unexpected error: Procedure 'usp_Childs_Name' expects parameter '@ChildsLast', which was not supplied. Microsoft OLE DB Provider for SQL Server Return code = -2147217904
Comma delimited string in nvarchar, matching with user id   (318 Views)
Okay heres the deal (I hope I can explain this clearly enough while being as breif as I can): I created a trouble ticket system (ASP site using MS SQL) where the trouble tickets were assigned to only one person, but now I need tickets to be assigned to multiple people. So in my ticket table I changed the assigned_to field to nvarchar and the user id's are comma delimited. On the users home page, when they sign into the site I show currently open tickets assigned to that user. Initially my SQL statement looked like this: strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, b.name as [Client] FROM tickets t, users u, business b where u.user_id = t.assigned_to and t.assigned_to = "& session("user_id")&" and t.status = 'In Progress' and b.business_id = t.business_id" Now as the field type has changed I went looking for something to help me sort thru the comma delimited field to find tickets assigned to the logged in user and am somewhat close with this: strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, b.name as [Client] FROM tickets t, users u, business b where u.user_id = t.assigned_to and (CHARINDEX(',"& session("user_id") &",',t.assigned_to)>0 OR CHARINDEX('"& session("user_id") &",',t.assigned_to)=1 OR CHARINDEX(',"& session("user_id") &"',t.assigned_to)=LEN(t.assigned_to)-1) and t.status = 'In Progress' and b.business_id = t.business_id order by b.name, t.ticket_id" or strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, b.name as [Client] FROM tickets t, users u, business b where u.user_id = "& session("user_id") &" and (PATINDEX('%,"& session("user_id") &",%',t.assigned_to)>0 OR PATINDEX('%"& session("user_id") &",%',t.assigned_to)=1 OR PATINDEX('%,"& session("user_id") &"%',t.assigned_to)=LEN(t.assigned_to)-1) and t.status = 'In Progress' and b.business_id = t.business_id order by b.name, t.ticket_id" neither of which work that well as it will return records that aren't assigned to that user at all and I am nto sure why. I just found charindex() and patindex() and barely know how to use them properly. Please help me as I am at my wits end.
what's the error in my mssql statement? :(   (216 Views)
hi i always used this type of command PHP Code: response.write("") response.write("") response.write("") response.write("") response.write("") to send out my data to mssql.. but problems will occur when the users fill in the CommonName fields with values with whitespaces and/or ' how should i fix this in java there's some method called preparedstatement.. how about in asp any help is greatly appreciated.
Syntax error near keyword 'BEGIN'   (171 Views)
never mind
error checking in ASP   (221 Views)
hi !! i have another problem in my coding... how can i add an error checking in my page because i have a customer registration page where a customer will input items the page... how can i have an error checking in the page, just like having an error like "username should not be more than 12 characters" or to have an error checking account like "the password you have entered is not the same" because there will be a field with "password" and "re-type password..."
Timeout expired error on SQL Server query with subquery   (342 Views)
hi, i have an ASP page that is querying an SQL database, and a SQL timeout expired '80040e31' error occurs on the recordset.open line. The query is resonably standard, except there is a correlated subquery to do a COUNT of 'other users' who registered lately against the same school. Is there a way to make the query more efficient, (uncorrelate the subquery or something) so that the timeout error does not occur I could take out the subquery, and then do a seperate query on each userID to see if there are 'other users' for that user, but i think that might be less efficient than using the subquery (only about 1 in 10 users has 'other users'). The users table has about 40000 records, and no field is very large, varchar 300 would be about the biggest.
ORA-12560: TNS:protocol adapter error   (203 Views)
hi ppl, i tried to connect oracle server installed in a remote machine located in the same network and oracle client is installed in my local machine. when i try to execute the SQL statements from my client machine i am experiencing the following problem :- ERROR: ORA-12560: TNSrotocol adapter error i had given the oracle server machine's IP address correctly in the "Net Configuration Assistant". kindly reply me at the earliest, regards, Ramamoorthy
How to trap error for duplicate key   (227 Views)
With VFP, I have created a Form based on remote view of a table in MS SQL Database, that have a primary key. If the record is new, after I have enter the primary key, I need to control if the code exist already. I have not problem with the dbf file, but I have some difficulty with SQL database. Someone can help me, in order to resolve this problem Thank you. Best
Querystring   (193 Views)
I'm trying to update one column from one table, but I need the information from other table to update it. So my querystring is like this UPDATE InventoryStockTotal, ShoppingCart SET InventoryStockTotal.dblUnitsocated = (InventoryStockTotal.dblUnitsStock + ShoppingCart.Quantity) WHERE InventoryStockTotal.strProductID = tblShoppingCart.strProductID AND ShoppingCart.strCartID = @strCartIDbut I get an error msg. "Incorrect syntax near ',' line 1.anyone can help me
Unicode character support: SQL no - Access yes.   (140 Views)
all I have a page with at the top, which supports Greek characters. Greek characters display correctly in the page except those coming from my database (SQL Server). They seem like I didn't have set the codepage value. I request these values and display them in a drop-down combo box. Strange thing is, exactly the same page worked fine when it requested data from Access, but now I am using SQL Server and not display Greek characters. The problem appears only in my web host's database. When I was working locally on my PC it appeared fine! Can anybody give ideas on the possible solution
Timeout error   (193 Views)
I have an excel program that runs every 10 minutes that gets data from files and inserts it in a table in the SQL server. Most of the time it runs ok. However when the network has heavy use, it generates a "Timeout" error. How do I prevent this