I have a following update statement that returns an error:
update job_schedule_rep
set tlbkup = (select Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuf
f(Right(Replicate('0', 6) + Convert(VarChar(8), tl.active_start_time), 6), 3
,0, ':'), 6, 0, ':')), 100),7) as 'Transaction Log Backup'
from
(select name, job_id, active_start_time
from sysjobschedules
where name like 'TLBkup%') as tl
join
(select name, job_id
from sysjobs
where name like 'TL Backup%') as sjt
on sjt.job_id = tl.job_id
where sjt.name not like '%Maintenance%'
order by sjt.name)
Here is an error:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
I have tried replacing '=' with in and exists but it didn't make any differe
nce.
Im getting this error when i try to change my value of my int RoleID: UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'Roles_Users_FK1'. The conflict occurred in database 'lyngso', table 'Roles', column 'RoleID'.The statement has been terminated.
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: UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'Roles_Users_FK1'. The conflict occurred in database 'lyngso', table 'Roles', column 'RoleID'.The statement has been terminated.
Source Error:
Line 373: tryLine 374: {Line 375: rowsAffected = dbCommand.ExecuteNonQuery();Line 376: }Line 377: finallyThe code that courses this error should be this code from my DatabaseHandler class:... queryString = "SELECT unitID FROM Units WHERE containerID = @containerID AND logDateTime = @logDateTime"; dbCommand = new SqlCommand(queryString, dbConnection); //ContainerID Param IDataParameter dbParam_containerID = new SqlParameter(); dbParam_containerID.ParameterName = "@containerID"; dbParam_containerID.Value = containerID; dbParam_containerID.DbType = System.Data.DbType.String; dbCommand.Parameters.Add(dbParam_containerID); //LogDateTime Param IDataParameter dbParam_logDateTime = new SqlParameter(); dbParam_logDateTime.ParameterName = "@logDateTime"; dbParam_logDateTime.Value = logDateTime; dbParam_logDateTime.DbType = System.Data.DbType.DateTime; dbCommand.Parameters.Add(dbParam_logDateTime);...The logDateTime param comes as a param to the method this code i located in. In my database the two tables are created as the following:CREATE TABLE [dbo].[Roles] ( [RoleID] [int] IDENTITY (1, 1) NOT NULL , [Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [RoleLevel] [int] NOT NULL ) CREATE TABLE [dbo].[Users] ( [UserID] [int] IDENTITY (1, 1) NOT NULL , [Firstname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Lastname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MobilPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [IsActive] [bit] NOT NULL , [RoleID] [int] NOT NULL ) To me it all looks fine, but my application doesn't agree with me....can anybody help here?
I am getting an error message when trying to run this update statement:
update table1 set col1 = (select substring(col2,1,2) + '-' + substring(col2,3,7) from table1)
Error message:
Server: Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
Hi,I am relatively new to SQL. I am using SQL 2000. I am trying toUpdate a field base in a criteria in a scond table.UPDATE Tbl1SET Tbl1.Row2 = '1'WHERE Tbl1.Row1 =(SELECT Tbl1.Row1FROM Tbl2, Tbl1WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fnCURRENT_TIMESTAMP () } >= Tbl2.Row3))Row 1 is the key between the two table. If I am doing only the selectbelow, I am getting the right value.SELECT Tbl1.Row1FROM Tbl2, Tbl1WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fnCURRENT_TIMESTAMP () } >= Tbl2.Row3)When I am running the entire querry, I am getting this error:Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.The statement has been terminated.What I am trying to do is to update a field in Tbl1 base on a date inTbl2. If the date is expire, I want to raise a flag, in Tbl1.Thank youPhilippe
Hi forum, im converting some code and have an issue with th following code!!! many thanks for good advice, Paul Line 55: SQLa = "UPDATE counted SET " & y & " = " & intClick & ", total = " & intTotal & " WHERE ID = " & RSpc.Fields("pc").ValueLine 56: Conn.Execute(SQLa) System.Runtime.InteropServices.COMException: Syntax error in UPDATE statement. Dim SQLa As String
I have place a lblmessage.text = ex.Message to trace what happen and it show me a syntax error in update happen. I don't know whats wrong with my syntax. Can someone help me on this?
'set up connection dim conn as new oledbconnection _ ("provider = microsoft.jet.oledb.4.0;" & _ "data source = c:aspnetdataanking.mdb")
sub page_load(sender as object, e as eventargs) if not page.ispostback then filldatagrid() end if end sub
sub submit (sender as object, e as eventargs) 'insert new data dim i,j as integer dim params(7) as string dim strtext as string dim blngo as boolean = true
j = 0
for i = 0 to addpanel.controls.count -1 if addpanel.controls(i).gettype is _ gettype (textbox) then strtext = ctype(addpanel.controls(i), _ textbox).text if strtext <> "" then params(j) = strtext else blngo = false lblmessage.text = lblmessage.text & _ "you forgot to enter a value for " & _ addpanel.controls (i).id & "<p>" lblmessage.style ("forecolor")= "red" end if j=j+1 end if next
sub dgdata_edit (sender as object, e as datagridcommandeventargs) filldatagrid(e.item.itemindex) end sub
sub dgdata_delete (sender as object, e as datagridcommandeventargs) dim strsql as string = "DELETE FROM tblusers " & _ "WHERE userid = " & e.item.itemindex + 1
executestatement(strsql) filldatagrid() end sub
sub dgdata_update (sender as object, e as datagridcommandeventargs) if updatedatastore(e) then filldatagrid(-1) end if end sub
sub dgdata_cancel (sender as object, e as datagridcommandeventargs) filldatagrid(-1) end sub
sub dgdata_pageindexchanged (sender as object, e as datagridpagechangedeventargs) dgdata.databind() end sub
function updatedatastore (e as datagridcommandeventargs) as boolean dim i,j as integer dim params(7) as string dim strtext as string dim blngo as boolean = true
j = 0
for i =1 to e.item.cells.count - 3 strtext = ctype(e.item.cells(i).controls(0), _ textbox).text if strtext <> "" then params(j) = strtext blngo = true j= j+1 else blngo = false lblmessage.text = lblmessage.text & _ "you forgot to enter a value<p>" end if next
if not blngo then return false exit function end if
executestatement (strsql) return blngo end function
sub filldatagrid (optional editindex as integer = -1) ' open connection dim objcmd as new oledbcommand _ ("select * from tblusers", conn) dim objreader as oledbdatareader
try objcmd.connection.open () objreader = objcmd.executereader() catch ex as exception lblmessage.text = "error retrieving from the " & _ "database." end try
dgdata.datasource = objreader if not editindex.equals(nothing) then dgdata.edititemindex = editindex end if
Hi All, When I use the following I get an error. I think it is because of duplicate records in my table.
Update person Set username = (Select username From update_person Where person.firsname = update_person.firstname and person.lastname = update_person.lastname)
There are a few users that have the same first and last name. How can I ignore the duplicate records and continue to update the table?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename] SET [No] = CASE WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa' ELSE 'Null' END
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000 UPDATE [dbo].[CC_Info_T] SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v' WHERE [Acct_Num_CH] IS NOT NULL WHILE @@ROWCOUNT > 0 BEGIN SET rowcount 10000 UPDATE [dbo].[CC_Info_T] SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v' WHERE [Acct_Num_CH] IS NOT NULL END SET rowcount 0
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB );
The error I'm getting is: Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
Dim lblock As Boolean chkChecked = lblock strSQL = "UPDATE CLIENTS SET " If blnCompleted = True Then strSQL = strSQL & "COMPLETED_DT = '" & Format(Now(), "MM/dd/yyyy") & "', " Else strSQL = strSQL & "LAST_SAVED_DT = '" & Format(Now(), "MM/dd/yyyy") & "', " End If strSQL = strSQL & "COMMENTS = '" & FixString(txtcomments.Text) & "' " _ & "WHERE client_ID = " & iclientID & ""I want to put my booleen value lblock to sql too, I probably need value of it, It is checkbox, called chkblock, . how would I include this to update statement database field for that BLOCK =
Hi, i nid help on update statement. I using 03 and a microsoft sql server 2000 database. I use a more simple example of my error. A Northwind Database is use to update the Region table(RegionDescription) User will 1st go in WebForm2.aspx and enter a id, if found will retrieve the data to WebForm1.aspx. User type "1" and retrieve Eastern to TextBox1. User can choose to update the table by typing in a diff word into TextBox1. But when i type any word(e.g East) the page is refresh back to Webform1.aspx with the not updated data and the database is also not updated. Any idea?
WebForm2.aspx.vb Imports System.Data.SqlClient Public Class WebForm2 Inherits System.Web.UI.PageWeb Form Designer Generated Code Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page hereEnd SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Session("id") = TextBox1.Text Response.Redirect("WebForm1.aspx")End Sub End Class
WebForm1.aspx.vb Imports System.Data.SqlClient Public Class WebForm1 Inherits System.Web.UI.Page Web Form Designer Generated CodeDim cnn As New SqlConnection("Data Source=(local); Initial Catalog=Northwind;User ID=******; Password=******") Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here Label1.Text = Session("id") retrieveTitle()End SubSub retrieveTitle() cnn.Open()Dim cmd As New SqlCommand cmd.CommandText = "SELECT * FROM Region WHERE RegionID = '" + Session("id") + "'" cmd.Connection = cnnDim dr As SqlDataReader dr = cmd.ExecuteReader() If dr.Read() Then TextBox1.Text = dr("RegionDescription").ToString End If cnn.Close()End SubSub UpdateTitle(ByVal title As String) cnn.Open()Dim sqlstr As String = "UPDATE Region SET RegionDescription = '" + title + "' WHERE RegionID = '" + Session("id") + "'" Trace.Write(sqlstr)Dim cmd As New SqlCommand(sqlstr, cnn) cmd.ExecuteNonQuery() cnn.Close()End SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
I have a SQL Table with the following columns ID, Date, Meeting, Venue, Notes What Update statement do i need to update a simple grid view in Visual Studio?I have been experementing but when i click update it updates the whole column insted of the one i was trying to update. Please can you help? Thanks
I need some help. please. Here is what I got. From the webpage I can pull the following data to update a table. update Vehicle set Name='TestUnitName' ,Make='TestMake', Model='TestModel', SoftwareVersion='TestVersion', DynamicChange='1', ProviderID='1', Description='TestDescription', VIN='TestVIN', IMEI='TestSIM', EngineTypeId=' ', Phone=' ', MobilePhoneProviderID='' where VehicleID=64 But I also get the error: "The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Vehicle_EngineTypes". The conflict occurred in database "Telemetry", table "dbo.EngineTypes", column 'EngineTypeId'.The statement has been terminated." How do I solve this issue? Actually what I am trying to do is, when EngineTypeId=' ' , I want to set is to NULL and same for the MobilePhoneProviderID. Any help would be appreciated. Thanks in advance.
I need to set a column value where the id is within a string. However, I need to set the column to a default value if the id is not within the string. Hope that was easy to understand!
ie:
This currently works...
SET @strSQL = 'UPDATE tblTest SET Archive = 1 WHERE RecID IN (' + @IDList + ')
If the ID is not in the list then I want that column set to 0. How can I do this?
Hi I use a update sub, the problem is that i got an error, the error is: Syntax error in UPDATE statement. I guess the UPDATE statement is: strUpdate = "Update tblUsers Set UserName=@UserName, Password=@Password, RetypePassword=@RetypePassword, Email=@Email, Comments=@Comments Where UserID=@UserID"
Remark:I use Acceess DataBase, exactly the same code works fine in SQL, i just changed the DataBase(From Access to SQL).
Is the problem can be in other place? Thank you very much for your assistance.
Hi, I'm having trouble writing this update statement and was wondering if anyone could help me out :
My database is sort of set up lilke this: There are 3 tables: Orders, OrderDetails and Inventory
Orders has a pk called OrderID. OD has several ProductIDs listed for that OrderID Inventory has 2 fields, the pk InventoryID(which is the same as ProductID) and QOH
So OD kinda looks like this: OrderID ProductID Quantity Cost 192 12 2 $10 192 3 1 $12 192 14 2 $50 193 12 1 $11 .... . ... ...
so what i want to do is take each productID for a specific orderid and decrement the inventory for it by OD.quantity
This is what I was trying
UPDATE Inventory Set QOH = QOH - @qty WHERE Inventory.InventoryID IN ( SELECT ProductID FROM OrderDetails WHERE OrderDetails.OrderID = @OrderID and @qty = OrderDetails.Quantity )
I am a SQL Novice - I have a table called 'users' in which I need to modify the user's email id e.g., from seanu@hotmail.com to seanu@yahoo.com and I have over 150 rows where I should be changing 'domain' name only..
Any help with an Update Statement is highly appreciated..
Is there any way to create a "dynamic" update statement in a stored procedure? Lets say I want to choose whether to update all columns in a table or just one. If I want to update just one column, is there a way to make the other columns keeping their values instead of NULL?
ID Field1 Field2 Field3 Field4 1 x y 100 0 2 xx zz 5 0 3 x y 200 1 4 a b 1 1 5 a b 2 0 6 k L 78 1 7 aa bb 25 1 8 k L 15 0
Now the problem is if I want to update a latest record's(if you go by ID)Field4 with 2 for the records where Field1 + Filed2 is unique, can I do this in a single query?
I can select the unique record by the following query
select Field1,Field2 from myTable group by Field1,Field2 having count(ID)>1
Hi first post, so apologies if this is in wrong forum.
I am needing to update a field in one table, where a particular value of another table is TRUE, and update that table based on a common field in both tables.
at the moment my query is:
IF EXISTS (SELECT * FROM _tblSnapShotAUSProfServicesOrderLines_Daily WHERE _tblSnapShotAUSProfServicesOrderLines_Daily.Produc tRelProductName like 'Prof%') UPDATE OrderHeader_details SET ServicesSold = 'Y' WHERE OrderHeader_details.OrderRef = _tblSnapShotAUSProfServicesOrderLines_Daily.OrderR ef
In its current format, I get the column prefix is incorrect which makes sense sort of. No matter which way I change this, I cannot get the query to work. I one form, it works but essentially performs two seperate statements, one with the select, and one with the update which unfortunately updates every row in the table.
I need help with a sql update statement i need it to convert a datetime value to a specific format. For some reason it statement will only update the row only when i convert the datetime value to a varchar, i ran it in mssql sql server management studio and it works, the database is a mssql 2000 i think. If i try to compare the datetime value and they are both DateTime values it fails, if i convert them to varchar and run them they succeed, what i also noticed is when when are date values are that the value i passed in the query is that they are in a different format, i guess the database stores them as mon/dd/yyyy hh:mm:ss. but when i run the query that failed i get this as the results of the values from variables @Date1 & @Date2: They are the same and they failed. So if anyone knows any thing any help is surely appreciated this really sucks.
Failed Date 1: Sep 23 2007 9:54PM Date 2: Sep 23 2007 9:54PM ---------------------------------------------
Need help with making this work in C#:
Code:
string sql = string.Format( "UPDATE [Contact Sheet] SET CallAttempt1 = {0}, CallAttempt2 = {1}, " + "CallAttempt3 = {2}, Status = '{3}' WHERE ContactID = {4} AND ModifiedDate = 'CONVERT(VARCHAR(50), {5}, 0)'", ..............................);
This statement ran in MSSQl Studio Management and works:
Code:
DECLARE @Date1 AS VARCHAR(50) DECLARE @Date2 AS VARCHAR(50)
SET @Date2 = CONVERT(DATETIME, '9/23/2007 9:54:40 PM', 0); SET @Date1 = (SELECT Convert(VARCHAR(50), ModifiedDate, 0) FROM [Contact Sheet] WHERE CustomerID = 22);
IF @DATE1 = @DATE2 BEGIN PRINT 'Success' END ELSE BEGIN PRINT 'Failed' -- PRINT @TmpDate PRINT 'Date 1: ' + @DATE1 PRINT 'Date 2: ' + @Date2 END
This one fails everytime:
Code:
DECLARE @Date1 AS DATETIME DECLARE @Date2 AS DATETIME
SET @Date2 = CONVERT(DATETIME, '9/23/2007 9:54:40 PM', 0); SET @Date1 = (SELECT Convert(DATETIME, ModifiedDate, 0) FROM [Contact Sheet] WHERE CustomerID = 22);
IF @DATE1 = @DATE2 BEGIN PRINT 'Success' END ELSE BEGIN PRINT 'Failed' -- PRINT @TmpDate PRINT 'Date 1: ' + CONVERT(VARCHAR(50), @DATE1, 0) PRINT 'Date 2: ' + CONVERT(VARCHAR(50), @Date2, 0) END