I have a 6 char field which has to be converted to a datetime. I thought I had it solved when I did this
convert(datetime,(left(dob,2)+'-'+substring(dob,3,2)+'-'+right(dob,2)))
Problem is with a date value of 081649
I get 08/16/2049 instead of 1949, where did I goof
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
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.
This is my code and I don't know why this error keeps coming out : PS : I did cursor to execute query.Th error showed is bold:
DECLARE RegCreatedDate CURSOR FOR
SELECT DISTINCT (CONVERT(NVARCHAR,CreatedDate,103))
FROM CA_Registration WHERE Month(CreatedDate)= @paMonthIn AND YEAR(CreatedDate)=@paYearIn
OPEN RegCreatedDate
FETCH NEXT FROM RegCreatedDate INTO @RegCreatedDate
WHILE @@FETCH_STATUS = 0
[Code] ....
I'm trying to select only July from show_held but I keep on getting the error message saying:
Conversion failed when converting date and/or time from character string.
I get error message after I write this code:
ANDshow.show_held = '&July&'
As you can see from the below code, How do I select July from times_held_in_July?
SELECTevent_name,
DATENAME (MONTH, show_held) AS times_held_in_July
FROMevent,
show
WHEREevent.show_id = show.show_id
Result:
event_name times_held_in_July
DressageJuly
Jumping July
Led in July
Led in September
Led in May
DressageApril
DressageJuly
Flag and PoleJuly
SELECTevent_name,
DATENAME (MONTH, show_held) AS times_held_in_July
FROMevent,
show
WHEREevent.show_id = show.show_id
ANDshow.show_held = '&July&'
Result:
Msg 241, Level 16, State 1, Line 24
Conversion failed when converting date and/or time from character string.
BEGIN TRAN;
INSERT INTO [dbo].[QuestManualProcess]
([ProcessFromDate]
,[LastProcessedFileDateStamp]
,[ProcessedOnDate]
[code]....
Conversion failed when converting date and/or time from character string/
I am trying to write a stored procedure that loops through the list of user tables, gets the record count for each one and write a record to an audit table with DATE, TABLENAME, RECORDCOUNT.I keep getting an error "Conversion failed when converting date and/or time from character string".Here is the script...
DECLARE @table nvarchar(500)
DECLARE @sql nvarchar(520)
DECLARE CursorSelect CURSOR FOR
select table_name from INFORMATION_SCHEMA.tables where table_name not like 'sys%' order by table_name
[code]....
I've imported a CSV file into a table in SQL Server 2012. It's a large file, 140,000+ rows, so I couldn't covert it to Excel first to preserve the date format due to Excel's row limit. In the CSV file, there were 3 column with date data in "31-Aug-09" format, and the import automatically transformed these in "31AUG09" format (varchar(50)) in SQL Server. Now I need to convert these 3 columns from varchar to datetime so I could work with them in date format.
I've tried several things,e.g,
select
convert(datetime,
right(admdate,4)+left(admdate,2)+substring(admdate,3,3))
or
select
convert(datetime,
substring(admdate,6,4)+substring(admdate,1,2)+substring(admdate,3,3))
but keep getting the same error message (Msg 241) "Conversion failed when converting date and/or time from character string".
All source and target date fields are defined as data type "smalldatetime". The "select" executes without error though when used with "insert into" it fails with the error:
Msg 295, Level 16, State 3, Line 25: Conversion failed when converting character string to small date-time data type..I am converting from a character string to smalldatetime since the source and target date columns are "smalldatetime". All other columns for the source and target are nvarchar(255). I assume there is an implicit conversion that I don't understand. In a test, I validated that all dates selected evaluate ISDATE() to 1.
USE [SCIR_DataMart_FromProd_06_20_2014]
GO
IF OBJECT_ID ('[SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]', 'U') IS NOT NULL
DROP TABLE [SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]
[code]....
I am trying to convert a decimal date value to a date value that I can use in formulas but am having a lot of trouble.My date value currently shows in decimal format YYYYMMDD. I want to convert this to a date so I can then find the number of days between two dates.I have tried convert (datetime, convert(varchar(8),left(qhstdt,8))) with qhstdt as my decimal date field but I receive the error message below:
Error: SQL0204 - CONVERT in *LIBL type *N not found.
I have also tried converting it using (year(QHSTDT)*10000+100*month(QHSTDT)+ day(QHSTDT))) but when I convert the dates using this formula, I can get an incorrect number of days when I try to subtract one from the other.What formula can I use to convert my YYYYMMDD field to a format that will allow me to compare number of days between two dates?
How can I create a field in a view that takes a date field andconverts it to a month?For instance, if I have a field called "CreatedOn" with a date of'2/22/2007', how can I create another field called "Month" that willsay "February"? Is there a built-in SQL function that does this (likeusing CONVERT) or do I need to write my own?Thanks!Lisa
View 1 Replies View RelatedI copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)
My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.
The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50))
I would like to have it -> 11/29/00 (datetime)
Any help is greatly appreciated!
JT
The goodness we share is rewarded twice!
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
View 1 Replies View RelatedI know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
View 1 Replies View RelatedHi 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
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
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 1Syntax 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 animalWHERE 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.
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...
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())')
hello all,is there a quick way to convert a zipcode of type int, to a 5 characterchar value ?e.g.declare @zip intdeclare @czip char(5)select @zip = 2109select @czip = convert(char, @zip)select @czipbut with @czip = 02109 instead of 2109 ?thanks in advance
View 2 Replies View RelatedDECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________
The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect
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
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?
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?
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
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.
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!
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)
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 RelatedHi,
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?
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
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