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/
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 --------------------~-->
$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/
