Right but in the C# code I set the SqlDbType as DateTime before I build the SqlParameter and it comes into the SP as a datetime so do I really need to convert it?
Travis D. Falls |Consultant, Raft.Net IT | 860.547.4070 | [EMAIL PROTECTED] -----Original Message----- From: Eric Cantrall [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 27, 2004 2:17 PM To: '[EMAIL PROTECTED]' Subject: RE: [AspNetAnyQuestionIsOk] converting datetime from character st ring Try this, I don't know if it will work but it's worth a shot SET @startDateTime = CONVERT(datetime, ''' + @startDateTime + ''') SET @endDateTime = CONVERT(datetime, ''' + @endDateTime+ ''') BTW, I'm not sure about logging but you can run the stored procedure from Query analyzer. You will have to put the variables in manually and execute it from Query analyzer. Then in Query analyzer it prints the steps it takes and shows what line in the code is breaking. --Eric -----Original Message----- From: Falls, Travis D (HTSC, CASD) [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 27, 2004 2:02 PM To: '[EMAIL PROTECTED]' Subject: RE: [AspNetAnyQuestionIsOk] converting datetime from character st ring I tried this and I am still getting the System.Data.SqlClient.SqlException: Syntax error converting datetime from character string. Error from the Microsoft application block when I tried to execute. Is their a way to log in SQL Server so I can trace out my dynamic sql? CREATE PROCEDURE [dbo].[PROC_getLogs] @count varchar(5)='20', @node varchar(50)=null, @server varchar(50)=null, @service varchar(50)=null, @subservice varchar(50)=null, @jobid varchar(10)=null, @type varchar(1)=null, @messid varchar(10)=null, @search varchar(255)=null, @startDateTime datetime = null, @endDateTime datetime = null AS DECLARE @queryString varchar(255) DECLARE @selectFromList varchar(255) DECLARE @LogView varchar(255) DECLARE @JobIDView varchar(255) DECLARE @NodeView varchar(255) DECLARE @ServerView varchar(255) DECLARE @ServiceView varchar(255) DECLARE @SubServiceView varchar(255) DECLARE @TypeView varchar(255) DECLARE @MessIDView varchar(255) SET @queryString = '' SET @selectFromList = '' SET @node = RTRIM(@node) SET @server = RTRIM(@server) SET @service = RTRIM(@service) SET @subservice = RTRIM(@subservice) SET @type = RTRIM(@type) SET @jobid = RTRIM(@jobid) SET @messid = RTRIM(@messid) SET @startDateTime = CONVERT(datetime, @startDateTime) SET @endDateTime = CONVERT(datetime, @endDateTime) if (RTRIM(@count) ='') BEGIN SET @count = '20' END if (@node is not null)and(RTRIM(@node) <> '') BEGIN Set @[EMAIL PROTECTED] + ' AND NODE =' + "'" + @node + "'" Set @selectFromList [EMAIL PROTECTED] + ',NODE' END if (@server is not null)and(RTRIM(@server) <>'') BEGIN Set @[EMAIL PROTECTED]' AND SERVER = '+ "'" + @server +"'" Set @selectFromList [EMAIL PROTECTED] + ',SERVER' END if (@service is not null)and(RTRIM(@service) <>'') BEGIN Set @[EMAIL PROTECTED]' AND SERVICE =' +"'"+ @service +"'" Set @selectFromList [EMAIL PROTECTED] + ',SERVICE' END if (@subservice is not null)and(RTRIM(@subservice) <>'') BEGIN Set @[EMAIL PROTECTED]' AND SUBSERVICE ='+ "'"[EMAIL PROTECTED] +"'" Set @selectFromList [EMAIL PROTECTED] + ',SUBSERVICE' END if (@jobid is not null)and(RTRIM(@jobid) <>'') BEGIN Set @[EMAIL PROTECTED] 'AND JOB_ID ='+"'"+ @jobid +"'" Set @selectFromList [EMAIL PROTECTED] + ',JOB_ID' END if (@type is not null)and(RTRIM(@type) <>'') BEGIN Set @[EMAIL PROTECTED]' AND TYPE ='+"'"+ @type+"'" Set @selectFromList [EMAIL PROTECTED] + ',TYPE' END if (@messid is not null)and(RTRIM(@messid) <>'') BEGIN Set @[EMAIL PROTECTED]' AND MESSAGE_ID ='+"'"+ @messid +"'" Set @selectFromList [EMAIL PROTECTED] + ',MESSAGE_ID' END if (@search is not null)and(RTRIM(@search) <>'') BEGIN Set @[EMAIL PROTECTED]' AND MESSAGE LIKE '+"'%"+ @search +"%'" END if ((@startDateTime <> null)AND (@endDateTime <> null)) BEGIN Set @[EMAIL PROTECTED]' AND datetime BETWEEN '+ "'"+ @startDateTime + "'" + ' AND ' + "'" + @endDateTime + "'" Set @selectFromList [EMAIL PROTECTED] + ',DATETIME' END SELECT @LogView = 'SELECT Top ' + @count + ' NODE, SERVER, SERVICE, SUBSERVICE, JOB_ID, TYPE, MESSAGE_ID, MESSAGE, DATETIME FROM MESSAGES WHERE 1=1' + @queryString + ' Order By DATETIME Desc' SELECT @JobIDView = 'SELECT DISTINCT JOB_ID as RaftID' [EMAIL PROTECTED] +' FROM MESSAGES WHERE 1=1 AND JOB_ID <> "" ' + @queryString + ' Order By JOB_ID ASC' SELECT @NodeView = 'SELECT DISTINCT NODE as Node' + @selectFromList + ' FROM MESSAGES WHERE 1=1 AND NODE <> "" ' + @queryString + ' Order By NODE ASC' SELECT @ServerView = 'SELECT DISTINCT SERVER as Server' + @selectFromList + ' FROM MESSAGES WHERE 1=1 AND SERVER <> "" ' + @queryString + ' Order By SERVER ASC' SELECT @TypeView = 'SELECT DISTINCT TYPE as Type' + @selectFromList + ' FROM MESSAGES WHERE 1=1 AND TYPE <> "" ' + @queryString + ' Order By TYPE ASC' SELECT @ServiceView = 'SELECT DISTINCT SERVICE as Service' + @selectFromList + ' FROM MESSAGES WHERE 1=1 AND SERVICE <> "" ' + @queryString + ' Order By SERVICE ASC' SELECT @SubServiceView = 'SELECT DISTINCT SUBSERVICE as SubService' + @selectFromList + ' FROM MESSAGES WHERE 1=1 AND SUBSERVICE <> "" ' + @queryString + ' Order By SUBSERVICE ASC' SELECT @MessIDView = 'SELECT DISTINCT MESSAGE_ID as MessID' + @selectFromList + ' FROM MESSAGES WHERE 1=1 AND MESSAGE_ID <> "" ' + @queryString + ' Order By MESSAGE_ID ASC' EXEC (@LogView) EXEC (@JobIDView) EXEC (@NodeView) EXEC (@ServerView) EXEC (@ServiceView) EXEC (@SubServiceView) EXEC (@TypeView) EXEC (@MessIDView) GO Travis D. Falls |Consultant, Raft.Net IT | 860.547.4070 | [EMAIL PROTECTED] -----Original Message----- From: Eric Cantrall [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 27, 2004 12:05 PM To: '[EMAIL PROTECTED]' Subject: RE: [AspNetAnyQuestionIsOk] converting datetime from character st ring Also, when you are converting datetime strings to dates always use the Convert() function if using MSSQL CONVERT(datetime, '10/17/2004') MSSQL knows how to convert the dates explicitly, but I always use it. If there is a problem with the data, then it will be caught here. You can also format the date like: CONVERT(datetime, '1/1/2004', 102) If you are not using MSSQL check out the CAST function. It does pretty much the same thing. HTH --Eric -----Original Message----- From: Ben Miller [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 27, 2004 11:36 AM To: [EMAIL PROTECTED] Subject: Re: [AspNetAnyQuestionIsOk] converting datetime from character string Whenever you piece together the SQL statement with DateTime strings, you need to put ' (single quotes) around them. SET @string = 'BETWEEN ''' + @datetime1 + ''' AND ''' + @datetime2 + '''' What you are seeing above is escaping the delimiter ' so that the resulting string is this: BETWEEN '1/1/2004' AND '1/10/2004' Dates need to be strings with ' ' surrounding them in order to work. Make sense? Otherwise you just get BETWEEN 1/1/2004 AND 1/10/2004 Which SQL Server will try to convert into a DateTime and it will not be able to. Ben Miller ----- Original Message ----- From: Falls, Travis D (HTSC, CASD) To: '[EMAIL PROTECTED]' Sent: Wednesday, October 27, 2004 7:26 AM Subject: [AspNetAnyQuestionIsOk] converting datetime from character string I am getting the following error when I try to submit a datetime between function to get a date range. Error: System.Data.SqlClient.SqlException: Syntax error converting datetime from character string. The error comes on the datagrid fill called within the application block. Any thoughts? C# code (using Microsoft application block for sql) SqlParameter[] myParameter = new SqlParameter[listOfArgs.Count]; SqlDbType type; int i = 0; IDictionaryEnumerator myEnumerator = listOfArgs.GetEnumerator(); while(myEnumerator.MoveNext()) { String Key = (String)myEnumerator.Key; type = SqlDbType.VarChar; object item; if(Key.IndexOf("DateTime")> 0) { type = SqlDbType.DateTime; item = (SqlDateTime)myEnumerator.Value; } else item = (String)myEnumerator.Value; myParameter[i] = new SqlParameter("@"+Key,type); myParameter[i].Value = item; i++; } DataSet ds = SqlHelper.ExecuteDataset(Global.connectionString, CommandType.StoredProcedure, "PROC_getLogs", myParameter); Stored Poc: CREATE PROCEDURE [dbo].[PROC_getLogs] @count varchar(5)='20', @node varchar(50)=null, @server varchar(50)=null, @service varchar(50)=null, @subservice varchar(50)=null, @jobid varchar(10)=null, @type varchar(1)=null, @messid varchar(10)=null, @search varchar(255)=null, @startDateTime DateTime = null, @endDateTime DateTime = null AS DECLARE @queryString varchar(255) DECLARE @selectFromList varchar(255) DECLARE @LogView varchar(255) DECLARE @JobIDView varchar(255) DECLARE @NodeView varchar(255) DECLARE @ServerView varchar(255) DECLARE @ServiceView varchar(255) DECLARE @SubServiceView varchar(255) DECLARE @TypeView varchar(255) DECLARE @MessIDView varchar(255) SET @queryString = '' SET @selectFromList = '' SET @node = RTRIM(@node) SET @server = RTRIM(@server) SET @service = RTRIM(@service) SET @subservice = RTRIM(@subservice) SET @type = RTRIM(@type) SET @jobid = RTRIM(@jobid) SET @messid = RTRIM(@messid) if (RTRIM(@count) ='') BEGIN SET @count = '20' END if (@node is not null)and(RTRIM(@node) <> '') BEGIN Set @[EMAIL PROTECTED] + ' AND NODE =' + "'" + @node + "'" Set @selectFromList [EMAIL PROTECTED] + ',NODE' END if (@server is not null)and(RTRIM(@server) <>'') BEGIN Set @[EMAIL PROTECTED]' AND SERVER = '+ "'" + @server +"'" Set @selectFromList [EMAIL PROTECTED] + ',SERVER' END if (@service is not null)and(RTRIM(@service) <>'') BEGIN Set @[EMAIL PROTECTED]' AND SERVICE =' +"'"+ @service +"'" Set @selectFromList [EMAIL PROTECTED] + ',SERVICE' END if (@subservice is not null)and(RTRIM(@subservice) <>'') BEGIN Set @[EMAIL PROTECTED]' AND SUBSERVICE ='+ "'"[EMAIL PROTECTED] +"'" Set @selectFromList [EMAIL PROTECTED] + ',SUBSERVICE' END if (@jobid is not null)and(RTRIM(@jobid) <>'') BEGIN Set @[EMAIL PROTECTED] 'AND JOB_ID ='+"'"+ @jobid +"'" Set @selectFromList [EMAIL PROTECTED] + ',JOB_ID' END if (@type is not null)and(RTRIM(@type) <>'') BEGIN Set @[EMAIL PROTECTED]' AND TYPE ='+"'"+ @type+"'" Set @selectFromList [EMAIL PROTECTED] + ',TYPE' END if (@messid is not null)and(RTRIM(@messid) <>'') BEGIN Set @[EMAIL PROTECTED]' AND MESSAGE_ID ='+"'"+ @messid +"'" Set @selectFromList [EMAIL PROTECTED] + ',MESSAGE_ID' END if (@search is not null)and(RTRIM(@search) <>'') BEGIN Set @[EMAIL PROTECTED]' AND MESSAGE LIKE '+"'%"+ @search +"%'" END if ((@startDateTime <> null)AND (@endDateTime <> null)) BEGIN Set @[EMAIL PROTECTED]' AND datetime BETWEEN '+ @startDateTime + ' AND ' + @endDateTime Set @selectFromList [EMAIL PROTECTED] + ',DATETIME' END SELECT @LogView = 'SELECT Top ' + @count + ' NODE, SERVER, SERVICE, SUBSERVICE, JOB_ID, TYPE, MESSAGE_ID, MESSAGE, DATETIME FROM MESSAGES WHERE 1=1' + @queryString SELECT @JobIDView = 'SELECT DISTINCT JOB_ID as RaftID' [EMAIL PROTECTED] +' FROM MESSAGES WHERE 1=1 AND JOB_ID <> "" ' + @queryString + ' Order By JOB_ID ASC' SELECT @NodeView = 'SELECT DISTINCT NODE as Node' + @selectFromList + ' FROM MESSAGES WHERE 1=1 AND NODE <> "" ' + @queryString + ' Order By NODE ASC' SELECT @ServerView = 'SELECT DISTINCT SERVER as Server' + @selectFromList + ' FROM MESSAGES WHERE 1=1 AND SERVER <> "" ' + @queryString + ' Order By SERVER ASC' SELECT @TypeView = 'SELECT DISTINCT TYPE as Type' + @selectFromList + ' FROM MESSAGES WHERE 1=1 AND TYPE <> "" ' + @queryString + ' Order By TYPE ASC' SELECT @ServiceView = 'SELECT DISTINCT SERVICE as Service' + @selectFromList + ' FROM MESSAGES WHERE 1=1 AND SERVICE <> "" ' + @queryString + ' Order By SERVICE ASC' SELECT @SubServiceView = 'SELECT DISTINCT SUBSERVICE as SubService' + @selectFromList + ' FROM MESSAGES WHERE 1=1 AND SUBSERVICE <> "" ' + @queryString + ' Order By SUBSERVICE ASC' SELECT @MessIDView = 'SELECT DISTINCT MESSAGE_ID as MessID' + @selectFromList + ' FROM MESSAGES WHERE 1=1 AND MESSAGE_ID <> "" ' + @queryString + ' Order By MESSAGE_ID ASC' EXEC (@LogView) EXEC (@JobIDView) EXEC (@NodeView) EXEC (@ServerView) EXEC (@ServiceView) EXEC (@SubServiceView) EXEC (@TypeView) EXEC (@MessIDView) GO Travis D. Falls |Consultant, Raft.Net IT | 860.547.4070 | [EMAIL PROTECTED] This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return email and delete this communication and destroy all copies. Yahoo! Groups Sponsor ADVERTISEMENT ---------------------------------------------------------------------------- -- Yahoo! Groups Links a.. To visit your group on the web, go to: http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/ <http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/> <http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/ <http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/> > b.. To unsubscribe from this group, send an email to: [EMAIL PROTECTED] c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. [Non-text portions of this message have been removed] Yahoo! Groups Sponsor ADVERTISEMENT <http://us.ard.yahoo.com/SIG=129eh567s/M=315388.5529720.6602079.3001176/D=gr <http://us.ard.yahoo.com/SIG=129eh567s/M=315388.5529720.6602079.3001176/D=gr > oups/S=1705006764:HM/EXP=1098977842/A=2372354/R=0/SIG=12id813k2/*https:/www. orchardbank.com/hcs/hcsapplication?pf=PLApply&media=EMYHNL40F21004SS> click here <http://us.adserver.yahoo.com/l?M=315388.5529720.6602079.3001176/D=groups/S= <http://us.adserver.yahoo.com/l?M=315388.5529720.6602079.3001176/D=groups/S= > :HM/A=2372354/rand=157112243> _____ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/ <http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/> <http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/ <http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/> > * To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED] e> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/ <http://docs.yahoo.com/info/terms/> > . [Non-text portions of this message have been removed] Yahoo! Groups Links Yahoo! Groups Sponsor ADVERTISEMENT <http://us.ard.yahoo.com/SIG=1298l1q1p/M=315388.5526708.6599542.3001176/D=gr oups/S=1705006764:HM/EXP=1098986678/A=2372354/R=0/SIG=12id813k2/*https:/www. orchardbank.com/hcs/hcsapplication?pf=PLApply&media=EMYHNL40F21004SS> click here <http://us.adserver.yahoo.com/l?M=315388.5526708.6599542.3001176/D=groups/S= :HM/A=2372354/rand=968062894> _____ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/ <http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/> * To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED] e> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of <http://docs.yahoo.com/info/terms/> Service. [Non-text portions of this message have been removed] Yahoo! Groups Links ------------------------ Yahoo! Groups Sponsor --------------------~--> $9.95 domain names from Yahoo!. Register anything. http://us.click.yahoo.com/J8kdrA/y20IAA/yQLSAA/saFolB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
