Converting String To Datetime Datatype?
Jul 19, 2014
I have a column which has 05MAY2006:04:34:00.000000 it is stored as varchar(25). I need to save it as datetime in the same column. I have tried using
update tablename
set columnname = (SUBSTRING(columnname,1,2) + '-' + SUBSTRING(columnname,3,3) + '-' +
SUBSTRING(columnname,6,4) + ' ' + SUBSTRING(columnname,11,8));
and then
alter table tablename
alter columnname datetime;
but later it shows up the error
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
How do I change it any other opinion or any modification for the above query.
View 2 Replies
ADVERTISEMENT
Sep 8, 2007
A SQL Server 2005 db has three date related-columns (MonthGiven,
DayGiven, YearGiven) each as smallint datatype. I would like to
create a DocDate column (datetime datatype) that combines the data from
the three existing date-related columns. I have tried casting and
simple concatentation without success. ALTER TABLE Details ADD DocDate DateTime NULL
UPDATE Details SET DocDate = CAST(MonthGiven AS DateTime)+ '/' + CAST(DayGiven AS DateTime) + "/" Cast(YearGiven As DateTime)
I think I need to be doing a Conversion instead of casting but
have been unable to implement info I have found in the SQL Server
Developer Center in my situation.
View 11 Replies
View Related
Aug 10, 2007
Hello,
I have a varchar column that inludes dates in the following fomat: 03032007? When I try to cast this to datetime, I keep getting "Arithmetic overflow error converting expression to data type datetime." error. Maybe someone has some ideas how to handle this?
Thanks!
View 4 Replies
View Related
Jul 26, 2007
Hi
What's wrong about
SELECT SUM(CASE WHEN [PO Date] BETWEEN CONVERT(Datetime, @FY + '/04/01')
AND CONVERT(Datetime, @FY -1 + '/03/31') THEN Quantity ELSE 0 END) AS Expr1,
[Item No_]
FROM table A
View 7 Replies
View Related
Jun 6, 2008
I have a dropdown list thats boudn to a SqlDataSource. The DataSource looks like this:
<asp:SqlDataSource ID="dsProgramList" runat="server" ConnectionString="<%$ ConnectionStrings:csData %>"
SelectCommand="SELECT DISTINCT [Program_Name] +','+ [Begin_Date] AS NAMEandDATE, [Course_ID], [LOC] FROM [ThisTable] WHERE ([LOC] = @LOC)">
<SelectParameters>
Where the dropdownlists text = NAMEandDATE and its value = Course_ID
When I select the LOC from the LOCdropdownlist, the dropdownlist in question updates, and an error "Erro converting datetime from character string" happens?
Any suggestions?
View 4 Replies
View Related
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
Jul 9, 2007
Hi all, first time post so go easy.
I have been trying to find a way of creating a datetime comprised of getdate() and a time that i specify. I am trying to do this as shown below:
Declare @Test datetime
Declare @Test1 Datetime
Set @Test = getdate()
SET @Test1 = CONVERT(datetime, datepart(y, @Test) + datepart(m, @Test)+ datepart(d, @Test)+' 16:30:00',120)
So, i am building a string using datepart and then adding the time at the end. The 120 after the final comma is to define the style (as i am sure you will all know anyway).
When i run this i get the following error:
Msg 245, Level 16, State 1, Line 35
Syntax error converting the varchar value ' 16:30:00' to a column of data type int.
If i take out the colons it runs but brings back the wrong date, the whole date seems to be dependant on the time part at the end.
I am sure this is a really simple problem and i am sorry if i am wasting you time but its late and i just cant see it. Any help would be fantastic.
View 2 Replies
View Related
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 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.
View 6 Replies
View Related
Mar 20, 2007
Stuart writes "Hi being new to this game
I have have an error when trying to inseet string into a table with datetime field.
the date is not that important its the time that I use in later steps
I am creating a global temp table and then inserting values into it
below is the code
-- create the temp table
Execute ( 'create table ##progsch0
([Time] [DateTime] , '
+ '[' + @day7 + '] [varchar](100) ,'
+ '[' + @day1 + '] [varchar](100) ,'
+ '[' + @day2 + '] [varchar](100) ,'
+ '[' + @day3 + '] [varchar](100) ,'
+ '[' + @day4 + '] [varchar](100) ,'
+ '[' + @day5 + '] [varchar](100) ,'
+ '[' + @day6 + '] [varchar](100) )')
set @Starttime = 'JUL 21,2006 5:30am'
I am doing the insert in this manor becuase the @Starttime
in code actually changes time and a new record in inserted into the temp table.
Set @SQL = 'Insert into ##progsch0 (Time)
Values(convert(varchar,Convert(datetime,'+ @Starttime +'),100))'
PRINT @SQL
execute sp_executesql @SQL
I may to doing this in the completely wrong manor.
Any help would be greatful "
View 1 Replies
View Related
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
Sep 24, 2007
Hello all,
New to the forums here. I'm not a beginner with SQL, but nor am I a SQL developer - network engineer who knows some scripting and the fundamentals of DB design/administration. There's the background.
I have a database of client information where a date is keyed in and stored as text. Because this is entered by end-users, the way it's entered varies - 1/1/2001, 01/01/2001, etc. Most use "1/1/2001" (note the date is not the same for each record)
I have 4,000 records to update and I need to try to convert the text string to the correct date - changing 1/1/2005 to the same date in proper date/time format. My database uses datetime as an integer calculating the number of days from 12/30/1800. Today's date would be 75508. Time is separated into different fields.
I can do the work to update the text to a single format to make the conversion easier, but I am having trouble locating the proper way to write a convert function to do this. I've searched online (which is how I came here) and have searched the forums without luck.
Any help would be greatly appreciated!!!
View 6 Replies
View Related
Aug 19, 2004
I have a stored procedure called from ASP code, and when it is executed, the stored procedure takes in a date as an attribute.
But since the stored procedure call is really just a string in the code, it is taking in the value as a string. So in my stored procedure, I want to convert a string value to a date value.
Any idea how this is done?
View 1 Replies
View Related
May 29, 2014
I am trying to create a stored procedure that Pulls in Chargeable and Non Chargeable hours for our employees however When I run the Stored Procedure I get this error "Conversion failed when converting date and/or time from character string." I am having a hard time figuring out were this is happening in the Stored Procedure. Also I would like to be able to Add a parameter that would be the StartDate and EndDate for which the stored procedure would pull time for.
ALTER PROCEDURE [dbo].[Chargeability]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[Code] ......
View 9 Replies
View Related
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
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
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
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
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
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
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
Nov 25, 2014
I have a table with a 25,000 records with fields like this:
Jan 1 196
Jun 29 195
Jan 22 196
Sep 17 195
Mar 22 193
Nov 3 197
Is there a quick way to add the zero onto the year, before I convert them into datetime format?
View 3 Replies
View Related
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
Sep 29, 2006
Hi,
I'm writing some SQL and want to convert the following expression into a date format in the SELECT list:
'01/'&Month(OrderDate)&'/'&Year(OrderDate)
Basically, I want to look at each order date, convert that date to the first of that month and GROUP BY this expression.
There is a CONVERT function, but I'm new to all this and can't seem to get it to work.
Any help would be gratefully received!
Cheers!
Keith
View 8 Replies
View Related
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
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
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
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
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
Jul 13, 2006
The source is a flat file with a column where dates are stored as: 07/12/2006 11:35am. In some cases the column is blank.
I need to import this into a table with a datetime column. In the data flow task, I get the error that conversion b/w String and DB_Timestamp is not allowed. First question is why does SSIS think its a DB_Timestamp column? Shouldnt it be DB_Date or DB_Time. Anyway, when I add a Data Conversion Task in the Data Flow, and cast to the source column to either DB_TimeStamp, DB_Date or DB_Time I get an error.
Please help. What am I doing wrong.
Asim.
View 7 Replies
View Related
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
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
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
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
