Date Rows Last Updated
How can I produce a list of date/times that each table in my database was last updated?
View Complete Forum Thread with Replies
Related Forum Messages:
Save Updated Date When Row Is Updated
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
View Replies !
DataSet Rows Being Deleted, But After The Update , The Sql Database Is Not Updated. The Delete Rows Still In The Database.
Stepping thru the code with the debugger shows the dataset rows being deleted. After executing the code, and getting to the page presentation. Then I stop the debug and start the page creation process again ( Page_Load ). The database still has the original deleted dataset rows. Adding rows works, then updating works fine, but deleting rows, does not seem to work. The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet. cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId); rowCountDb = cDependChildDs._ClientDependentChild.Count; for (row = 0; row < rowCountDb; row++) { dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0]; dr_dependentChild.Delete(); //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0); //cDependChildDs._ClientDependentChild.Rows.Remove(0); /* update the Client Process Table Adapter*/ // cDependChildTA.Update(cDependChildDs._ClientDependentChild); // cDependChildTA.Update(cDependChildDs._ClientDependentChild); } /* zero rows in the DataSet at this point */ /* update the Child Table Adapter */ cDependChildTA.Update(cDependChildDs._ClientDependentChild);
View Replies !
#of Rows Updated
Is there a command that will tell me the number of rows that are updated in a statement. I would like to put this in an Stored Procedure and pass the #rows updated back out.
View Replies !
Get Total Updated Rows Effected By A Statement
This was a usual day today in office and i was working on a requirement in which i was needed to fetch the total number of rows effected by an update query, so I asked my best code mate "Google" and to my surprised there was not enough correct answers at least the one i was looking for.There were suggestions that you can use a select statement for the updated rows and make it like a select (count) which works fine, but just looking into the SQL server books online, it shows that there is even a better way to do it.After the update statement in my stored procedure i used "@@ROWCOUNT" with a select statement and it works like a charm.so the little find for my first ever post on asp.net is that there is a better way to find the total updated rows by a query Example: DB: Northwind , Table Employeesupdate employees set extension='1234'select @@ROWCOUNT This will return 9 (default rows in this table) as the rows effectedHope this helps
View Replies !
Undo Updated Rows In Sql Server 2005
currently i m developing a web application with using sql server 2005 and i was testing yesterday a sql update query with sql server management studio. in my update query i forgot to put where condition and now all the rows of table are updated. is there any solution to undo this and retrieve all rows back? Regards Selena
View Replies !
Fetch Only Inserted And Updated Rows From Source
Hi, I am trying to create a SSIS package, which will extract data from a SQL server view and populate the data in our local SQL server database tables. My objective is to get the data from the view such that only inserted and updated rows are fetched from the view. Note: the view does not expose any updated date type of column thru which I can check. So I guess I have to compare each and every field with my destination table row's fields. I would appreciate any suggestions on how to approach the problem. Thanks in advance.
View Replies !
Last Date Stored Proc Updated???
Is there such a date/time?I see the Created date on the list of stored procs, but really want aDate Last Updated. After changing code for 3 hours, I tend to forgetwhich procs I've worked on, and which need to be move to production.any simple way to keep track of the last procs played with?thanks in advance...Join Bytes!
View Replies !
Finding Last Updated/modified Date
Hi, I am using SQL Server 2000 and have the following questions: 1. How do I know the last updated (data) date using system objects or any other method? 2. How do I know the last modified date of a table using system objects or any other method? 3. How do I know when a table is last accessed Any help is appreciated Thanks for your time and help in advance Kumar
View Replies !
ERROR - The Row Value(s) Updated Or Deleted Either Do Not Make The Row Unique Or They Alter Multiple Rows.
i am getting the above error on my database i have 2 rows with the same info on and another 2 with the same info on. example: ID username password 1 bob bob 1 bob bob 1 john john 1 john john I know this is a fault with ms sql 2005 however how do i fix it? Ive found this link which explains everything but how do i start a query. I tried clicking on new query and copying the code. What is table1 meant to be? the database is dbl.tbl_admin. It wont find my database. Im not sure how to do it anyway. I need to change it though as its my admin password and Ive given it out to web design companys http://geekswithblogs.net/allensb/archive/2006/07/27/86484.aspx Can some 1 read the above page and give me full instructions, I dont know what im doing thanks info@uktattoostudios.co.uk
View Replies !
Updating Modified/updated Date Column?
If your tables contain created and modified/updated dates what is the best practice for these? 1. Should you use UTC dates? 2. Do you use a default for the creation date (I assume yes)? 3. Should you create a trigger to handle the last update date? Or do you update the column directly in your stored procedures that modify data? Also, as an aside if you store the user who created/updated the record do you store a foreign key reference to the user table or do you store the username as a varchar? Of course I know you'd normally store the fk, but I wasn't sure if the "logging" nature of the column suggests storing a string value.
View Replies !
Sql Server 2005 And Stored Procedures - Updated Date?
Hi, I was wondering, is it really true that in Sql Server 2005, there is no updated date -property for stored procedures? There is a created date -property, but it's not useful, because stored procedure's get updated often. This is a huge disadvantage for me, and I was really waiting for ms to fix this problem after sql server 2000, but no. Does anyone figured out any solution to this problem? Br, Riika
View Replies !
Linked Reports Not Being Updated When Master Report Is Updated
Since updating to SQL Server 2005 SP2 I've noticed two things about Linked Reports. 1. I do a lot of 'Snapshot' reports. With SP-1 if I updated a master report and made any changes to the Parameter List - it undid all my custom parameter changes on linked versions (restored to the Master Reports Defaults). While this is no longer happening with SP2 - it is still 'unhiding' the parameters. 2. With SP-1 if I added/deleted columns or made other changes to the report structure - the linked reports would pick up on the changes with their next refresh. With SP-2 I'm finding that I have to 'Re-link' the linked report back to the master report before the changes are refreshed. This is very time consuming especially with each report having 8 or more Snapshot reports pre-set up. Am I missing something - or is this a 'bug'... Any help would be appreciated...
View Replies !
Can &"Date Modified&" Col Be Automatically Updated W/o Trigger For Each Table?
Hello, I am using SQL Server 2005 and ASP.NET 2.0. We have a very simple content management system where we have to keep track of date last modified for each row in all of our content tables. I know there's a "timestamp" datatype that is used for replication scenarios, but is there anything similar that I can use to set up a date_modified column for each of my content tables that will automatically update with GETDATE() whenever anything in a given row is updated? Or do I have to create a date_modified column of smalldatetime datatype and write a trigger on update for EVERY single table of content that I have in the database? It seems there should be an easier way to do this than to write 20 triggers for my 20 content tables. Thanks!
View Replies !
How To Find Out Date/time When Row Was Updated Last Time
Hello, we need to track date/time of last update for each record in a table. As we understand it, we can't use field type Timestamp as this type does not use dates/times. Is there any SQL function available which we can bind to a column or do we really have to use triggers? Greetings from Mannheim, Germany Ricardo
View Replies !
How To Find Out Date/time When Row Was Updated Last Time
Hello, we need to track date/time of last update for each record in a table. As we understand it, we can't use field type Timestamp as this type does not use dates/times. Is there any SQL function available which we can bind to a column or do we really have to use triggers? Greetings from Mannheim, Germany Ricardo
View Replies !
Grouping Rows By Just The Date
I store data in a table using a column named InsertTimestamp which is a datetime format. I now want to report on rows by just the date. I have been able to do this by converting the datetime like such: convert(varchar, inserttimestamp, 107) This reurns the data correctly however is very ineffiecnet. DOes anyone know another easy way around this dilemna???
View Replies !
Get Rows For Latest Date
Hello! I have a table something like this: ID INTEGER Info VARCHAR (actually several columns but that is not important here) DAT DateTime For each ID there are several dates and for each of these dates there are several rows with different info. I would like to select the latest info for each ID. For example: ID - DAT - Info 1 - 2007-02-01 - Info1 1 - 2007-02-01 - Info2 1 - 2006-02-01 - Info3 2 - 2007-05-05 - Info4 2 - 2007-02-01 - Info5 2 - 2006-02-01 - Info6 I would like to get: Info1 Info2 Info4 This has to be done in one Query. Can anybody help me?
View Replies !
Count Rows In A Date Interval
I have a problem that I can't quite get started on solving.I have a table of asset statuses. Each time the asset status changes, anew row is inserted into the table.CREATE TABLE dbo.Tbl_EMStatusHistory (EMStatusID int IDENTITY (1, 1) NOT NULL ,EMSessionID int NULL ,AssetID int NOT NULL ,AssetStatus int NOT NULL ,StatusComment varchar (250) NULL ,StatusDate smalldatetime NOT NULL ,InsertUser sysname NOT NULL ,InsertDate smalldatetime NULL ,CONSTRAINT PK_EMStatusHistory PRIMARY KEY NONCLUSTERED(EMStatusID))Here is some sample data:EMStatusID AssetID AssetStatus StatusDate DeviceType4772622624OUT2003-10-05Monitor3810022624IN2003-10-16Monitor4726122624OUT2003-10-25 Monitor3819322624IN2003-11-02 Monitor3917122624RV2004-05-02 MonitorFor asset 22624, the current (most recent) status = RV. Before that, itlastchanged status on 2003-11-02, when it was IN. So it was IN from2003-11-02 to2004-05-02.I need to produce a report that counts the devices by type that were INon a given report date,like so:ReportDateMonitorsTransmitters05/01/2004342605/02/2004373005/03/2004393205/04/20043531The end user supplies the date range.I am unsure how to approach this. I came up with oneEinsteinian-Newtonian method that involved three temp tables, a gardenhose, and a duck, and it was getting uglier by the minute. Has anyonehad experience with a requirement like this? Thanks, Dave"Are you thinking what I'm thinking, Pinky?"*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Archiving Rows Using A Date Field
Hi all, Would anyone be able to point me in the right direction for creating a procedure to archive rows using a DATE field value as the determinant for selecting the rows to be moved to a backup table? I'd really appreciate any assistance that anyone can provide. Thanks, Tom C.
View Replies !
Incorrect Date In Column For 25,000 Rows
I have a table with a column in it called Date, which is of the type DateTime, and for the last two years I have been adding data which I found out was incorrect. My dates are all a day in the future, so I need to reduce each date by one day. I can easily use a select script to reveal the 25,000 rows which are all incorrect dates. But I can't figure out how to update each and every row to subtract one day from each date. So where I have: 26/01/2005 I would like to have: 25/01/2005 and of course for every record. Obviously way too many to do manually :-( Can anyone show me a script that will get what I'm after. Tia Tailwag
View Replies !
Query To Return Multiple Date Rows
HI there, Can someone please help with a query I have? Basically I want to return all rows in a table that have multiple date entries that are different. For example: 163610737464753422005-12-30 00:00:00.000 163610737464753592006-03-10 00:00:00.000 This security 1636 has two entries in the DB with different dates. They are lots of securities with multiple entries with the same date but I need a list of the ones with different dates. Any ideas please? Thanks!!!!! S
View Replies !
Creating SQL Statement That Returns Rows ONLY Between A Certain Date?
I maintain a simple employment (job) tracking web application. Jobs can be set to 5 different statuses: Open, Closed, Filled, Pending, or Cancelled. There is a table in the database called statusLog, which records everytime a job is set to opened, or set to closed, etc. It records the job number, the date it was changed, and what the job was changed to. Here is a short example of what a few entries might look like: Status Date Job number Open10/6/2002 2:34:56 PMTEST2845 Hold10/11/2002 12:19:29 PMTEST2845 Closed10/29/2002 2:00:54 PMTEST2845 Pending10/25/2002 3:37:06 PMTEST2877 What I need to do is write an SQL query that will return to me all entries in this table, between two certain dates, that ONLY have entries during those dates. Basically I need to know how many "new" jobs were set to open during a month. I can easily just do a count of how many jobs were set to open, but this will not give a count of "new" jobs. Example: during june a job could be set to open. Then in july it could be set to pending. Then in august, it could be re-opened, and set to open. If I ran this query for the month of August, it would return that job as being opened in august. But it wasn't a new job, meaning it had already been in the system in previous months. Is there some way I can select all "Open" jobs, between a certain date, that do not exist anywhere in the table previous to the date it was entered? This would give me a result set containing only new jobs. The only way I've thought of yet is to get a result set of all jobs simply set to open during a month, then one by one for each record, go back and run another SQL query to see if it exists in the table anywhere other than in that month. This seems horribly inefficient to me however, as I do not want to be doing 34,000 independent SQL calls for every single "open" job it finds during a certain month. thanks
View Replies !
Select Rows Where Int Formatted Date &< GetDate()
Hey there, I have a field called renew_date which is of (int) type, and represents a date in the format 'yyyymmdd'. Eg. If the renew date were yesterday the field would contain: 20080525. I need a select statment that will determine if the date contained in this field is '<' todays date. I have wrote a select statement that works, however I'm wondering if there is a better way to handle this. select * from sometable where renew_date < convert(int, convert(varchar, DATEPART(yyyy, getDate())) + convert(varchar, Right('0' + Convert(VarChar(2), DATEPART(mm, getDate())),2)) + convert(varchar, Right('0' + Convert(varchar(2), DATEPART(dd, getDate())),2))) Any help you may provide is greatly appreciated. Thanks --Mike
View Replies !
Selecting Rows From SQL Server Dbs With A Where Clause Using The System Date.
Hello i currently have a website that has an SQL server 2005 dbs that stores appointments. I would like to do a select statement in my sqldatasource that selects all the records that have an 'appointmentDate' more than 2 weeks after the current date (ie the system date). I am stuck on the SQL statement i need to produce to achieve this. I was thinking along the lines of SELECT * FROM appointments WHERE appointmentDate > System.Date + 14; However this is clearly not the right SQL statement. Any help would be appreciated. Many thanks, James.
View Replies !
Creating Rows Based On Date Range From Another Table
I wish to build a table based on values from another table.I need to populate a table between two dates from another table. Usingthe START_DT and END_DT, create records between those dates.I need a new column that is the days between the date and the MID_DTThe data I wish to end with would look something like this:PERIOD DATE DAY_NO200602 2005-07-06 -89200602 2005-07-07 -88200602 2005-07-08 -87<...>200602 2005-10-02 -2200602 2005-10-03 -1200602 2005-10-04 0200602 2005-10-05 1<...>200602 2005-12-18 75CREATE TABLE "dbo"."tblDates"("PERIOD" CHAR(6) NOT NULL,"START_DT" DATETIME NULL,"MID_DT" DATETIME NULL,"END_DT" DATETIME NOT NULL)INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)
View Replies !
SQL 2005 Return A List Of Missing Rows By Date?
Hi, I have written a reporting application which has a SQL2005 backend. An import routine into SQL, written by a 3rd party, frequently fails. The main problems are missing rows in certain tables. I am going to write an SP that will accepts a from and to date. I then want to search for rows of type X between those dates that do not exist so we then know between a date range, we have no data for these XYZ days. I have this working by returning all rows between the dates into a dataset, sorted by date, and then running through the rows and testing if the next rows date is the next expected date. This works but I think is a very poor solution. This is all done on the client in C#. I want to learn and implement the most efficent way of doing this. My only solution in a SP was to make a temporary table of all dates between the date range for row type X and then do a right outer join against the data table, returning all rows which are missing. Something like this: SELECT twmd.date FROM #temp_table_with_all_dates ttwad RIGHT OUTER JOIN table_with_missing_date twmd ON ttwad.date = twmd.date WHERE twmd.date IS NULL Would this be a good, efficent solution, or should I just stick to my processing of a dataset in C#? Many thanks in advance, CB
View Replies !
Delete Rows From A Table Matching A Calculated Date
Hello I am new to SSIS and learning as I go. Any guidance to my questions would be appreciated. I wrote a script that takes the current date and subtracts a number of days/months from this date. I then attempted to use an SQL Task as a select with a parameter using the calculated date from the script. I was not successful in doing this. While performing searches on the WEB with the hopes of finding a solution I came upon the following text in the Microsoft forum under EXECUTE SQL TASK. When you use an OLE DB Connection Manager, you cannot use parameterized subqueries because the Execute SQL task cannot derive parameter information through the OLE DB provider. However, you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task. Having come upon this statement I moved on to putting together an OLE DB SOURCE with a Flat File Destination. The SQL that I wrote is: SELECT BP_ID, INVC_NBR, INVC_DT, BUS_ADD_DT FROM DW.CUST_SALE_ADDR WHERE (BUS_ADD_DT = ?) The flat file destination was mainly used to confirm the select. Having confirmed my select, I changed the select in the OLE DB SOURCE as follows: DELETE FROM DW.CUST_SALE_ADDR WHERE (BUS_ADD_DT = ?) I also removed the Flat File Destination. Needless to say when I tried to run the package I did not get very far as a package validation error was encountered since there were no output columns. Can you share how I should go about peforming the delete as described from the table based on a calculated date? And am I not understanding the comment regarding the SQL Statement and the use of parameters? Thank you and God Bless.
View Replies !
I'm Trying To Get The Last Activity Date From W/in A Table Where There Are Multiple Rows Per Employee
Hi, I have been struggling trying to design a query that will alow be to select the most recent date in a table and I'm obviously not having much luck This is basically the table layout, note each employee can have multiple rows with different dates Employee_ID Last_Name First_Name Evaluation_Date Evaluation_Score 1 Jones Tom 01/04/07 40 1 Jones Tom 01/.12/07 50 1 Jones Tom 04/01/08 60 2 Smith Ed 02/14/05 70 2 Smith Ed 03/18/06 80 3 Brown John 06/23/04 80 3 Brown John 12/23/04 79 3 Brown John 01/07/06 50 3 Brown John 10/22/08 69 What I'd like to do would be to write some thing that would return the following, just the last date of the evaluation & whatever relevant data is in the table Employee_ID Last_Name First_Name Evaluation_Date Evaluation_Score 1 Jones Tom 04/01/08 60 2 Smith Ed 03/18/06 80 3 Brown John 10/22/08 69 I've looked at select distinct and the date operatives with out any success. Thanks Much Vince
View Replies !
Select Rows Based On Multiple Date Conditions
Hi, I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today. I'm not sure how to do this. SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything. I like to end up with a column containing the count of rows <= today and a column with rows where date > today. Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row? Thanks, Marc
View Replies !
How To Archiv Table To Another Table With Unique Number For All Rows Once + Date
need help how to archiv table to another table with unique number for all rows once + date time (not the second only day time +minute) i need whan i insert to the another table add 2 more fields (unique number , date_time ) this is the table 1 i select from ID fname new_date val_holiday ---------------------------------------------------- 111 aaaa 15/03/2008 1 111 aaaa 16/03/2008 1 111 aaaa 18/03/2008 1 111 aaaa 19/03/2008 1 111 aaaa 20/03/2008 1 111 aaaa 21/03/2008 1 222 bbb 02/05/2008 3 222 bbb 03/05/2008 3 222 bbb 04/05/2008 3 222 bbb 05/05/2008 3 222 bbb 06/05/2008 3 222 bbb 07/05/2008 3 222 bbb 08/05/2008 3 222 bbb 09/05/2008 3 333 ccc 03/04/2008 4 333 ccc 04/04/2008 4 this is the table 2 i insert into ---------------------------------- ID fname new_date val_holiday unique number date_time -------------------------------------------------------------------------------------------------------------------- 111 aaaa 15/03/2008 1 666 15/04/2008 17:03 111 aaaa 16/03/2008 1 666 15/04/2008 17:03 111 aaaa 18/03/2008 1 111 aaaa 19/03/2008 1 666 15/04/2008 17:03 111 aaaa 20/03/2008 1 666 15/04/2008 17:03 111 aaaa 21/03/2008 1 666 15/04/2008 17:03 222 bbb 02/05/2008 3 666 15/04/2008 17:03 222 bbb 03/05/2008 3 222 bbb 04/05/2008 3 666 15/04/2008 17:03 222 bbb 05/05/2008 3 666 15/04/2008 17:03 222 bbb 06/05/2008 3 666 15/04/2008 17:03 222 bbb 07/05/2008 3 666 15/04/2008 17:03 222 bbb 08/05/2008 3 666 15/04/2008 17:03 222 bbb 09/05/2008 3 666 15/04/2008 17:03 333 ccc 03/04/2008 4 666 15/04/2008 17:03 333 ccc 04/04/2008 4 666 15/04/2008 17:03 for evry archiv table to another table (insert) i need to get a unique number + date time (not the second only day time +minute) next insert ...... ID fname new_date val_holiday unique number date_time -------------------------------------------------------------------------------------------------------------------- 111 aaaa 15/03/2008 1 667 15/04/2008 17:15 111 aaaa 16/03/2008 1 667 15/04/2008 17:15 111 aaaa 18/03/2008 1 111 aaaa 19/03/2008 1 667 15/04/2008 17:15 ......................... .....................................................................667 15/04/2008 17:15 next insert ...... ID fname new_date val_holiday unique number date_time -------------------------------------------------------------------------------------------------------------------- 111 aaaa 15/03/2008 1 668 15/04/2008 08:15 111 aaaa 16/03/2008 1 668 15/04/2008 08:15 111 aaaa 18/03/2008 1 111 aaaa 19/03/2008 1 668 15/04/2008 08:15 ......................... .....................................................................668 15/04/2008 08:15 TNX
View Replies !
Counting Rows By Date (was &"Help On Query&")
I hate to ask such silly helps..but I'm missing something here..need help. I have a table having columns for createddate and deleteddate. The data gets created and deleted periodically and I need to find out the number of created,deleted and remaining number of records on each day. This query works, but takes a lot of time...not sure if there is a more better way to do this.. Please help SELECT CAST(createddate AS DATETIME) AS createdDate, Created, Deleted, Remaining FROM( SELECT CONVERT(VARCHAR,createdon,102) AS CreatedDate, COUNT(1) created, (SELECT COUNT(1) FROM table ta2 WHERE CONVERT(VARCHAR,ta2.deletedon,102) = CONVERT(VARCHAR,ta.createdon,102)) Deleted, ((SELECT COUNT(1) FROM table ta1 WHERE CONVERT(VARCHAR,ta1.createdon,102) <= CONVERT(VARCHAR,ta.createdon,102)) - (SELECT COUNT(1) FROM table ta1 WHERE CONVERT(VARCHAR,ta1.deletedon,102) <= CONVERT(VARCHAR,ta.createdon,102))) Remaining FROM table ta WHERE CONVERT(VARCHAR,createdon,102) >= (GETDATE() - 90) GROUP BY CONVERT(VARCHAR,createdon,102) ORDER BY CONVERT(VARCHAR,createdon,102) DESC) AS tmp
View Replies !
How?: Group By Date And Count Rows In Group
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation. this is the best I can come up with: Code: SELECT DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts FROM log_directory_contacts GROUP BY post_date The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date. What am I doing wrong? Thanks!
View Replies !
Get Rows Between Current Date And Current Time
I've this query SELECT t1.ID, t1.Date, t1.Time, t1.VALUE FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) Let's say, current date is 8 AUG 2005 and current time is 2045 So, i will get ID | Date (this is datetime) | Time (this is integer) | Value -------------------------------------------------- 204 | 8/1/2005| 2359 | 90 205 | 8/1/2005| 2250 | 99 206 | 8/1/2005| 1950 | 88 ... ... 207 | 8/7/2005| 1845 | 77 208 | 8/7/2005| 2255 | 77 209 | 8/7/2005| 2140 | 77 Can someone can show me to filter data between t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow ID | Date (this is datetime) | Time (this is integer) | Value -------------------------------------------------- 204 | 8/1/2005| 2359 | 90 205 | 8/1/2005| 2250 | 99 ... ... 207 | 8/7/2005| 1845 | 77 I only have this query, SELECT t1.ID, t1.Date, t1.Time, t1.VALUE FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) lack of idea to put the TIME condition. Plz help me..
View Replies !
How To Tell If A Row Is Updated
Hi Im doing a simple update in my SP: update users set Name = @Name where id=@userID and password=@password i want to know if a row gets updated. for example if the userID and password dont match then the row will not get updated.so i want some way to tell if a row has been updated. how do i do this?thanks
View Replies !
Updated
Thanks i just re tested again i think i had the database selected on the wrong one and was getting incorrect results. Seems to be working just as i thought. Snapshot got record 1 here then if udpate to 2 then this does not update the 1 to 2 Cheers
View Replies !
SQL Help: Get The Record I Just Updated
Hi - apologies if this is not the right forum for this - I've searched a bit and this seems to be the best fit. I have the following problem: I want to update records in a table that fit certain criteria. The way the insert logic works make sure that there will always be only one record that fits the criteria and I'd like to get the ID value of that record once the update went through. So here is what I tried: 1 UPDATE Timeslot 2 SET StartTime = @StartTime, EndTime = @EndTime 3 WHERE (ProfessionalID = @ProfessionalID) AND (ProviderLocationID = @ProviderLocationID) AND (RequestID IS NULL) AND (StartTime > @StartTime) AND 4 (EndTime < @EndTime); 5 SELECT SCOPE_IDENTITY() My hope was that the select scope_identity would return the Timeslot ID of the row that was affected, but it doesn't. How do I get that row? Thanks!!! Oliver
View Replies !
Query Cannot Be Updated Because The FROM
Hi everybody, I am a total noob conserning ASP, but I am willing to learn We have a sql2005 SRV(hosted by our ISP, so limited access) and a ASP based forum (WEB WIZ) When I try to login I get this error: Support Error Code:- err_SQLServer_loginUser()_update_USR_CodeFile Name:- functions_login.aspError details:-Microsoft OLE DB Provider for ODBC DriversQuery cannot be updated because the FROM clause is not a single simple table name.Can somebody tell me whats wrong? Thanx in advance. Gerry de Bruijn!
View Replies !
Get Last Updated Records?
If I update a recordset a group of records using dynamic SQL where I update the TOP n records, is it possible to get the set of records that was updated? CREATE PROCEDURE usp_Structural_ScheduleComponent @cProject char(7), @cComponentID char(10), @iPour int, @iQuantity int, @iAvailable int OUTPUT, @dtCast datetime OUTPUT AS SET @dtCast = convert(char(10), getdate(), 120) DECLARE @cSql varchar(500) SET @cSql = 'UPDATE tbStructuralComponentSchedule SET PourNumber = ' + CAST (@iPour AS VARCHAR) + ', ScheduledDate = ' + '''' + CAST(@dtCast AS VARCHAR) + '''' + ' WHERE EntryID IN ( SELECT TOP ' + CAST(@iQuantity AS VARCHAR) + ' FROM tbStructuralComponentSchedule ' + ' WHERE fkProjectNumber = ' + '''' + @cProject + '''' + ' AND fkComponentID = ' + '''' + @cComponentID + '''' + ' AND IssueDate IS NOT NULL' + ' AND ScheduledDate IS NULL' + ' ORDER BY EntryID DESC)' EXEC(@cSql) IF(@@ERROR <> 0 OR @@ROWCOUNT < = 0) RAISERROR('Failed to add components to pour!',16,1) SELECT @iAvailable = SUM(CASE WHEN IssueDate IS NOT NULL AND ScheduledDate IS NULL THEN 1 ELSE 0 END) FROM tbStructuralComponentSchedule WHERE fkProjectNumber = @cProject AND fkComponentID = @cComponentID GO -- Is there a way to return the recordset that were modified in the update? Mike B
View Replies !
|
TRACKER
