Converting Datetime From Character String

Dec 2, 2004

Can anyone tell me what this error message means and how I can correct it on my statement:


Code:

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



Here's my query statement:


Code:

SELECT animalid AS "Animal ID",
name AS "Name", categoryid AS "Category ID",
DATENAME(MONTH, dateBorn) + ' ' +
DATENAME(DAY, dateBorn) + ', ' +
DATENAME(YEAR, dateBorn) AS "Date of Birth"
FROM animal
WHERE categoryid = 'Cat' AND dateBorn = 'May%'



I know it has something to do with: dateBorn = 'May%' because
when I took out Quote: AND dateBorn = 'May%' I get results, but I need to narrow it down to list the cats born in May.

View 6 Replies


ADVERTISEMENT

Converting Datetime From Character String

Jun 28, 2004

Hi there,

I have the following code:

Dim CityTown As String = Ctype(Request.Querystring("CityTown"), String)
Dim Suburb As String = Ctype(Request.Querystring("Suburb"), String)
Dim SuburbValue As String = Ctype(Request.Querystring("Suburb"), String)
Dim Rooms As String = Ctype(Request.Querystring("Rooms"), String)
Dim Rent As String = Ctype(Request.Querystring("Rent"), String)

Dim DateToday = DateTime.Now
Dim mySQL AS String
If suburbValue = "- All -" Then
mySQL = "SELECT propListID, propListExpires, propBuildType, propRoomNumber, propRentPerWeek, propDescription, propCityTown, propSuburb FROM tblPropertyList WHERE [propCityTown]='" & CityTown & "' AND [propRentPerWeek]<= '" & Rent & "' AND [propRoomNumber]='" & Rooms & "'AND [propListExpires] >='" & DateToday & "' "


However I am having trouble getting the Date to work as part of my SQL String. I keep getting the following error:

Syntax error converting datetime from character string.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.

Source Error:


Line 30: MyDA = New SqlDataAdapter (mySQL, myConn)
Line 31: myDS = New DataSet()
Line 32: myDA.Fill(MyDS)
Line 33:
Line 34: dlPropertyListing.Datasource = MyDS.Tables(0)


Source File: K:detailsdetailspropertylisting.aspx Line: 32


Any ideas how to resolve this?? I do not want to use Parameters in my statement.

Thanks in advance,

TCM

View 3 Replies View Related

Converting Datetime From Character String

Aug 14, 2007

I am trying to select all members from a SQL db who have a renewal date btw to dates inputted into two text fields, but I am now getting the error above. This is where the problem is coming in...

If strStartDate <> "" and strEndDate <> "" Then
If bParam = True Then
StrSQL = strSQL & " AND "
Else
StrSQL = strSQL & " WHERE deleted=0 AND "
End If
strSQL = strSQL & "renewal_due BETWEEN '%" & Replace(strStartDate,"'","''") & "%' AND '%" & Replace(strEndDate,"'","''") & "%' "
End If


Any help would be greatly appreciated!

Thank you...

View 7 Replies View Related

Conversion Failed When Converting Datetime From Character String.

Jan 17, 2008

Hi,
I am getting the following error when
executing the ExecuteInsert in the code below..:
 
Conversion failed when converting
datetime from character string.



    private bool
ExecuteInsert(String quantity)   
{[snip]       
con.Open();       
SqlCommand command = new SqlCommand();       
command.Connection = con;       
TextBox TextBox1 =
(TextBox)FormView1.FindControl("TextBox1");       
Label 1 = (Label)FormView1.FindControl("Label3");       
Label 2 = (Label)FormView1.FindControl("Label13");       
command.CommandText = "INSERT INTO Transactions (etc,Date,etc)
VALUES (etc,@date,@etc)";        
command.Parameters.AddWithValue([snip]);       
command.Parameters.AddWithValue([snip]);        command.Parameters.AddWithValue("@date",
DateTime.Now.ToString());        
command.Parameters.AddWithValue([snip]);       
command.Parameters.AddWithValue([snip]);       
command.ExecuteNonQuery();       
con.Close();       
command.Dispose();       
return true;    }    protected
void Button2_Click(object sender, EventArgs e)   
{        TextBox TextBox1 =
FormView1.FindControl("TextBox1") as TextBox;       
bool retVal = ExecuteUpdate(Int32.Parse(TextBox1.Text));       
if (retVal)           
Response.Redirect("~/URL/EXTENSION.aspx");       
Insert();    }    private
void Insert()    {       
TextBox TextBox1 = FormView1.FindControl("TextBox1") as
TextBox;       
ExecuteInsert(TextBox1.Text);    }}  Thanks if someone can help!Jon 

View 1 Replies View Related

Conversion Failed When Converting Datetime From Character String

Jun 2, 2008

I am trying to write a SP in Query Analyzer before I start the code from a web form to acces it.
Here is the SQL:
-------------------------------------------
DECLARE @cReportGroups Varchar(500)DECLARE @StartDateRange DateTime
DECLARE @EndDateRange DateTime
SET @cReportGroups = '''VCCEddieBauer'',''VCCEddieBauerDirect'', ''VCCEddieBauerOrder'', ''VCCEddieBauerOvernight'', ''VCCEddieBauerSpanish'', ''VCCEddieBauerCustomerService'''
SET @StartDateRange = '09-16-2007'
SET @EndDateRange = '01-31-2008'
BEGIN
Declare @sql As Varchar(800)
Set @sql = 'SELECT ttalkacd, cName, dIntervalStart, cReportGroup' +
' FROM IAgentQueueStats ' +' WHERE dIntervalStart >= ' + @StartDateRange
---- ' AND dIntervalStart <= ' + EndDateRange  +
---- ' AND cReportGroup IN (' + @cReportGroups + ')' +
---- ' AND cHKey3 = ''*''' +
-- ' ORDER BY dIntervalStart' --+
---- ' COMPUTE SUM(ttalkacd)'
--Print(@sql)
exec (@sql)
END
-----------------------------------------------
Just trying to get the first >= filter working before I fill in the >= working to complete the date range logic.
But I keep getting this conversion error.
Why does SQL think that anything is a character string?All dates are declared as DateTime.I have tried a bunch of combinations like Convert(DateTime, @StartDateRange, 101).Nothing seems to work.

View 2 Replies View Related

Syntax Error Converting Datetime From Character String

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

Syntax Error Converting Datetime From Character String - HELP PLEASE!

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

Syntax Error Converting Datetime From Character String

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

Msg 241: Syntax Error Converting Datetime From Character String

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

Syntax Error Converting Datetime From Character String

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

SQL Syntax Error Converting Datetime From Character String

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

Syntax Error Converting Datetime From Character String.

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

Syntax Error Converting Datetime From Character String.

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

Conversion Failed When Converting Datetime From Character String.

Mar 6, 2008



Good Morning:


I received the above error message when I try to convert a field to a date.

Here is the line of sql:

convert(datetime, cast(EligibilityEndDate as char(8)), 112) as EligibilityEndDate,


EligibilityEndDate is stored as an integer in our source system, so dates display 20080201.

Looks like the data is not a correct date.
If I just bring in the field as is, and load results into a text file or Access, I can't see the offending record.
The query returns around 320K records.

Is there another way of finding the invalid record(s)?

Any help appreciated.

Thanks.

Mike




View 4 Replies View Related

Help With Exception: Conversion Failed When Converting Datetime From Character String.

Mar 7, 2007

Hi,
I am trying to insert an entry into my database, but I keep on getting the following error: "Conversion failed when converting datetime from character string."  My query is a parameterized T-SQL query (i.e. "INSERT INTO blah (name, dob) VALUES (@name, '@dob')"), and I have initialized the SqlCommand object by doing:  sqlCmd.Parameters.AddWithValue("@name", "Captain America");
sqlCmd.Parameters.AddWithValue("@dob", birthdate.ToString("MM/dd/yyyy h:mm tt");

sqlCon.Open();
sqlCmd.ExecuteNonQuery();
sqlCon.Close(); 
 The dob field in the table 'blah' is a smalldatetime datatype, and in the code above, the birthdate object is a DateTime object. 
I can insert into my database using a normal T-SQL through Management Studio,
For example,  INSERT INTO blah (name, dob) VALUES ('Captain America', '11/27/1923 12:34 PM')
 But it doesn't work when I try doing it the way I have listed above.  It's really annoying because I don't know how to debug or if it is possible to debug the query.  So first off, I want to ask, does anyone know how why this exception is occuring?  Secondly, is it possible to debug the query? and if yes, how?  And finally, is there a way for me to extract the T-SQL query sent to the SQL Server with the parameters filled in their values? For example, after adding all parameters to the SqlCommand via sqlCmd.Parameters.AddWithValue(), can I get the T-SQL query that is sent to the server?

View 3 Replies View Related

'Syntax Error Converting Datetime From Character String' With Sp_executesql

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

Conversion Failed When Converting Datetime From Character String When Using Where Clause

Oct 22, 2007

I have written the following query to extract data splitting a text string into separate fields. This query works fine and returns results.



WITH a (company_id, user_id, project_id, customer_id, action, importdate, groupid, accountnumber, amount, notes, created, updated) AS

(

select

company_id,

user_id,

project_id,

customer_id,

action,

CAST(SUBSTRING(params, PATINDEX('%[ImportDate=%', params)+13, 19) AS datetime) AS ImportDate,

SUBSTRING(params, PATINDEX('%GroupID=%', params)+8, 1) AS GroupID,

SUBSTRING(params, PATINDEX('%AccountNo=%', params)+10, 7) AS AccountNumber,

CAST(SUBSTRING(substring(params, 0, len(params)), PATINDEX('%Amount=%', params)+7, 10) AS decimal(18,2)) AS Amount,

notes,

created,

updated

from


database.dbo.tablename

where

customer_id is not null and

PATINDEX('%GroupID=%', params) <> 0 and

PATINDEX('%ImportDate=%', params) <> 0 and

PATINDEX('%AccountNo=%', params) <> 0 and

PATINDEX('%Amount=%', params) <> 0

)

select

company_id,

user_id,

project_id,

customer_id,

action,

importdate,

DATEPART(year, importdate) AS importyear,

DATEPART(month, importdate) AS importmonth,

groupid,

accountnumber,

amount,

notes,

created,

updated

from

a




The problem arises when I try to do a WHERE on either the importyear or importmonth field.



WITH a (company_id, user_id, project_id, customer_id, action, importdate, groupid, accountnumber, amount, notes, created, updated) AS

(

select

company_id,

user_id,

project_id,

customer_id,

action,

CAST(SUBSTRING(params, PATINDEX('%[ImportDate=%', params)+13, 19) AS datetime) AS ImportDate,

SUBSTRING(params, PATINDEX('%GroupID=%', params)+8, 1) AS GroupID,

SUBSTRING(params, PATINDEX('%AccountNo=%', params)+10, 7) AS AccountNumber,

CAST(SUBSTRING(substring(params, 0, len(params)), PATINDEX('%Amount=%', params)+7, 10) AS decimal(18,2)) AS Amount,

notes,

created,

updated

from


database.dbo.tablename

where

customer_id is not null and

PATINDEX('%GroupID=%', params) <> 0 and

PATINDEX('%ImportDate=%', params) <> 0 and

PATINDEX('%AccountNo=%', params) <> 0 and

PATINDEX('%Amount=%', params) <> 0

)

select

company_id,

user_id,

project_id,

customer_id,

action,

importdate,

DATEPART(year, importdate) AS importyear,

DATEPART(month, importdate) AS importmonth,

groupid,

accountnumber,

amount,

notes,

created,

updated

from

a


where DATEPART(year, importdate) = 2007

I get


Conversion failed when converting datetime from character string.

but when I use ISDATE() to validate the importyear it returns nothing indicating that the actual importdate is a valid date. Can anyone help please?

Thanks

View 11 Replies View Related

Syntax Error Converting Datetime From Character String (2000)

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

Using UNION With 2 SELECT Statements Gives 'Conversion Failed When Converting Datetime From Character String.'

Apr 30, 2008



Good day all.

Let's see if I can stump you with this one.....




Code Snippet
SELECT Account.New_DistributorAccountNumber, Account.New_NameonBill, ActivityPointer.Subject, ActivityPointer.CreatedOn AS ExprX
FROM Account INNER JOIN
ActivityPointer ON Account.AccountId = ActivityPointer.RegardingObjectId
UNION
SELECT Account_1.New_DistributorAccountNumber AS Expr1, Account_1.New_NameonBill AS Expr2, ActivityPointer_1.CreatedOn AS Expr19,
ActivityPointer_1.Subject AS Expr20
FROM Account AS Account_1 INNER JOIN
Contact AS Contact_1 ON Account_1.AccountId = Contact_1.AccountId INNER JOIN
ActivityPointer AS ActivityPointer_1 ON Contact_1.ContactId = ActivityPointer_1.RegardingObjectId







gives the error mentioned above. Each select statement, when run separately, works fine. Any clues?

Thank you in advance

View 4 Replies View Related

CASE Throws Conversion Failed When Converting Datetime From Character String Exception

May 1, 2008



Hi All,

I have SQL query as:


SELECT CASE @sortcolumn

WHEN 'TYPE' THEN TYPE_TXT

WHEN 'DATE' THEN CHECKOUT_TS

WHEN 'ACCT' THEN ACCT_NBR

WHEN 'FIRST' THEN FIRST_NM

WHEN 'LAST' THEN LAST_NM

WHEN 'DAYS' THEN DATEDIFF(dd, CHECKOUT_TS, GETDATE())

ELSE DELIVER_ID END

FROM REQUEST

If @sortcolumn has 'DATE' or 'DAYS' it runs fine....any other value, I get an exception "Conversion failed when converting datetime from character string". All columns except CHECKOUT_TS are of type varchar.

Any help would be greatly appreciated.

Thanks,
Shashi

View 3 Replies View Related

ERROR:Syntax Error Converting Datetime From Character String. With Stored Procedure

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

Error 241: Syntax Error Converting Datetime From Character String

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

Converting Character Data To DATETIME

Aug 2, 1999

For our DataWarehouse, we get several date fields from our mainframe system in a character format. When this data was loaded into SQL Server 6.5 using the CONVERT(DATETIME...) function, any dates containing 'bad' data would simply be replaced by a NULL automatically by the DBMS. We are now going to SQL 7.0, and I have found that when it hits a bad date it terminates the stored procedure, resulting with no update.

Is there a straightforward way around this? Possibly a script that will scrub the data, replacing bad data with NULLs? I'm trying to avoid writing something that will take the number of days per month and determine if it is valid.

Thanks

Michael

View 1 Replies View Related

Syntax Error Converting Datetime From Character St

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

Converting Date And / Or Time From Character String

Oct 22, 2013

My Query is as shown below

Declare @FROMDATE DATETIME
Declare @THRUDATE DATETIME
Declare @Parm Varchar(250)
set @FROMDATE = '09/25/2013'
set @THRUDATE = '09/28/2013'
set @Parm = 'FROMDATE=' + @FROMDATE + ';THRUDATE=' + @THRUDATE

I am getting an error at above line saying that

Conversion failed when converting date and/or time from character string.

View 8 Replies View Related

Conversion Failed When Converting From A Character String To Uniqueidentifier.

Jul 2, 2006

This is probably a simple wuestion but i would appreciate some help
I am trying to get and insert the userId into a table called "Orders"
I got the user id as
 Dim CustomerId As Object = Membership.GetUser(User.Identity.Name).ProviderUserKey.ToString
The button on click command is
 worldshop.ShoppingCart.CreateOrder(CustomerId,.........................
The Function Has the parameter for Customer Id as follows
Dim dbParam_CustomerId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter            dbParam_CustomerId.ParameterName = "@CustomerId"            dbParam_CustomerId.Value = CustomerId            dbParam_CustomerId.DbType = System.Data.DbType.String            command.Parameters.Add(dbParam_CustomerId)
When i run this I am getting the error message
"Conversion failed when converting from a character string to uniqueidentifier."
Can anyone tell me where I am going wrong
many thanks
Martin

View 5 Replies View Related

Conversion Failed When Converting From A Character String To Uniqueidentifier.

Apr 10, 2007

Hi, i have a problem, i keep getting this Error.
I want to insert an uniqueidentifier using a textbox, i use the following code to insert.
SqlDataSource1.InsertParameters["RWID"] = new Parameter("RWID", TypeCode.String, RWID);
SqlDataSource1.Insert();
The databasetype is an uniqueidentifier of that column.
Anyone who can help me with this problem?

View 3 Replies View Related

Conversion Failed When Converting From A Character String To Uniqueidentifier. - PLEASE HELP!!!

Jul 21, 2007

I am trying to store a unique identifier that is text into a field in a SQL DB that is type uniqueidentifier and I get the follow error message.
 
Conversion failed when converting from a character string to uniqueidentifier.
My Code is shown below:
comSQL.Parameters.AddWithValue("@PROPERTYID", Format(Request.QueryString("ID").ToString, "{0:########-####-####-####-############}"))
This has worked before but isnt' anymore.  Any ideas?

View 2 Replies View Related

Datetime Conversion From Character String Failed?

Dec 8, 2011

Conversion failed when converting datetime from character string. I am getting this error while inserting values in the DB from my application. I have done insertion before too , but i never receive this strange error but when I insert the same thing manually then i didn't get this error ...

The format I used is also yyyy-mm-dd then how to solve this error?????????

View 1 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type

Oct 12, 2006

I am newbie in asp and sql, and I am using VS & SQL expresswhen I try to submit I get following error"Conversion failed when converting character string to smalldatetime data type"Following is my insert statement<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@DropDownList1','@DropDownList2','@DropDownList3','@TextBox6','@DropDownCalendar1','@DropDownCalendar2','@DropDownCalendar3','@TextBox1','@TextBox2','@TextBox3','@TextBox4','@TextBox5','@TextBox7')"></asp:SqlDataSource>These are @DropDownCalendar1','@DropDownCalendar2','@DropDownCalendar3' defined as datetime in database.I would appriciate if somebody could help.

View 7 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type.

Mar 25, 2007

Hello, I have problem with this code.(This program presents - there is GridView tied to a SQL database that will sort the data selected by a dropdownList at time categories. There are  2  time categories in DropDownList - this day, this week.
 Problem: when I choose one categorie in dropDownlist for examle this week and submit data on the server I got this error.
Conversion failed when converting character string to smalldatetime data type.
Here is code:
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
 
 
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
 
string datePatt = @"yyyymmdd";
// Get start and end of day
DateTime StartDate = DateTime.Today;
 
string @StartDate1 = StartDate.ToString(datePatt);
 
string @EndDate = StartDate.AddDays(1).ToString(datePatt);
// Get start and end of week
string @startOfWeek = StartDate.AddDays(0 - (int)StartDate.DayOfWeek).ToString(datePatt);
string @startOfNextWeek = StartDate.AddDays(7 - (int)StartDate.DayOfWeek).ToString(datePatt);
 
 
switch (DropDownList1.SelectedValue)
{
 
case "1":
// day
SqlDataSource1.SelectCommand = "SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" + "WHERE" + "[DATE] >=" + "'@StartDate1'" + " AND [DATE] < " + "'@EndDate'";
break;
case "2":
//week
SqlDataSource1.SelectCommand = "SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" + "WHERE" + "[DATE] >=" + "'@startOfWeek'" + "AND [DATE] <" + "'@startOfNextWeek'";
break;
 
}
 
}
 
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
<style type="text/css">
body {
font: 1em Verdana;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
&nbsp;&nbsp;
 
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"
Style="z-index: 100; left: 414px; position: absolute; top: 22px">
<asp:ListItem Selected="True" Value="1">jeden den</asp:ListItem>
<asp:ListItem Value="2">jeden tyden</asp:ListItem>
</asp:DropDownList>
 
<asp:GridView ID="GridView1" runat="server" Style="z-index: 102; left: 228px; position: absolute;
top: 107px" DataSourceID="SqlDataSource1" AutoGenerateColumns="True">
 
</asp:GridView>
&nbsp; &nbsp;<br/> <br/>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=CRSQLEXPRESS;
Initial Catalog=MyConn;Integrated Security=True"
ProviderName="System.Data.SqlClient"></asp:SqlDataSource>
</div>
</form>
</body>
</html>
 

View 4 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type

Oct 3, 2007

I am running the following query:
 select distinct hqvend, hqvprd,fprod,idesc,ilead,sum(cast(fqty as int) )as fqty, (cast(hqvend as varchar(5))+'/'+ltrim(rtrim(fprod))+'/'+cast(ilead as varchar(3))) as Keydata,'L1/'+rtrim(fprod) as LocPartno from tblkfpl01 inner join hqtl01 on (fprod=hqprod) inner join iiml01 on (hqvend=ivend and hqprod=iprod) where  cast(cast(hqeff as varchar(8)) as smalldatetime) <= (Select CONVERT(varchar(8), getdate(), 1)) and  cast(HQDIS as varchar(8)) >= (Select CONVERT(varchar(8), getdate(), 1))  and hqvend like '134%' group by hqvend,fprod,hqvprd,idesc,ilead   order by hqvend,fprod
The bold sections are giving me the error message. The query works as written above, butis only evaluating on one date, contained in hqeff. However, when I try to modify the second date, HQDIS, by casting it the same way as the one before it:
 select distinct hqvend, hqvprd,fprod,idesc,ilead,sum(cast(fqty as int) )as fqty, (cast(hqvend as varchar(5))+'/'+ltrim(rtrim(fprod))+'/'+cast(ilead as varchar(3))) as Keydata,'L1/'+rtrim(fprod) as LocPartno from tblkfpl01 inner join hqtl01 on (fprod=hqprod) inner join iiml01 on (hqvend=ivend and hqprod=iprod) where  cast(cast(hqeff as varchar(8)) as smalldatetime) <= (Select CONVERT(varchar(8), getdate(), 1)) and  cast(cast(HQDIS as varchar(8)) as smalldatetime) >= (Select CONVERT(varchar(8), getdate(), 1))  and hqvend like '134%' group by hqvend,fprod,hqvprd,idesc,ilead   order by hqvend,fprod
I get the error message. I need to select based on both dates (hqeff AND HQDIS), but cannot seem to be able to it.... Both fields are of type Dedimal, with a length of 8, and dates stored in them look like: 20071003 (YYYYMMDD).
Any suggestions?

View 2 Replies View Related

Syntax Error Converting Character String To Smalldatetime Data Type.

Aug 8, 2005

Hi guys and gals,I have the following code:CREATE PROCEDURE searchRecords(     @searchFor  NVarchar (25)  = NULL,     @fromDate  NVarchar (25)  = NULL,     @toDate  NVarchar (25)  = NULL)
AS
IF @fromDate IS NOT NULL     DECLARE @fromDateString VarChar(200)     SET @fromDateString = '  CONVERT(smalldatetime, ''' + @fromDate + ''') '
IF @toDate IS NOT NULL     DECLARE @toDateString VarChar(200)     SET @toDateString = '  CONVERT(smalldatetime, ''' + @toDate + ''') '
SELECT * FROM MIPR WHERE      ID = @searchFor      OR DESC_QTY = @searchFor      OR REQ_ACTIVITY = @searchFor      OR ACC_ACTIVITY = @searchFor      OR MANYEARS = @searchFor      OR TECH_POC = @searchFor      OR RESOURCE_POC = @searchFor      OR SI_DATE BETWEEN @fromDateString   AND  @toDateString;GOit creates an error that I can't seem to fix. the error I get is:Syntax error converting character string to smalldatetime data type.How can I fix this? Thanks in advance!

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved