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 Complete Forum Thread with Replies
Related Forum Messages:
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 The Hour Of A Date Time
i have a query i need to calulate the HH of a datetime field example Dateval Need to get 10/01/2007 7:48 10/01/2007 7:00 10/01/2007 7:49 10/02/2007 7:00 10/01/2007 7:50 10/03/2007 7:00 10/01/2007 7:51 10/04/2007 7:00 10/01/2007 7:52 10/05/2007 7:00 10/01/2007 7:53 10/06/2007 7:00 10/01/2007 7:54 10/07/2007 7:00 10/01/2007 7:55 10/08/2007 7:00
View Replies !
Help Writing Query (find The Entry With The Closest Time Given A Time)
Hi, I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL. Here's what I have so far: Code: select * from TICK_D where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D) But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". Does anyone know how i could do this? Thanks a lot for any help!
View Replies !
Conversion Of Oracle Date Time To Sql Server Date Time In SSIS
This is driving me nuts.. I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database. I'm struggling with this for a quite a while and I'm not able to get it working. I tried the oracle query something like this, SELECT (TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE FROM TBLA this gives me an output of 20070511 23:06:30:000 the space in MM : SS is intentional here, since without that space it appread as smiley I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error The value could not be converted because of a potential loss of data I'm struck with error for hours now. Any pointers would be helpful. Thanks
View Replies !
Add Time To Datetime Value And Split Into Date And Time
Hi i have the following situation. in my database i have a datetime field (dd/mm/yy hh:mms) and i also have a field timezone. the timezone field has values in minutes that i should add to my datetime field so i have the actual time. afterwards i split the datetime into date and time. the last part i can accomplish (CONVERT (varchar, datetime, 103) as DATEVALUE and CONVERT (varchar, DATETIME, 108) as TIMEVALUE). could anybody tell me how i can add the timezone value (in minutes) to my datetime value ? i do all the calculations in my datasource (sql). Thanks V.
View Replies !
How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone
Hi all, I have created a report in SSRS 2005 which is being viewed by users from different Time Zones. I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone. For example if the date is August 07, 2007 10:00 AM UTC, then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST. Similarly for other Time Zones it should display the time accordingly. Is this possible in SSRS 2005? Any pointers will be usefull... Thanks in advance sudheer racha.
View Replies !
Determining Last Time A Table Was Updated (6.5, 7.0, 2k)
I am trying to find a global way of when the last time a row in one of my tables was updated or data inserted. I say global because I don't want to drill down through each table looking for modified rows. I am a DBA of several hundred databases and want to retire those no longer being used. Is there a column of a system table that has this info.
View Replies !
Splitting SQL Server Date/Time Column Into Access Date Column And Access Time Column
I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time. What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column? Thanks, Steve
View Replies !
Find Time Ranges
Find Time Ranges I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
View Replies !
Find Time Ranges
Find Time Ranges I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
View Replies !
How Do I Convert A Unix Date/Time Field To A Date When The The SQL DB Stores That Data As Char 11?
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days). I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss. The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help? Please advise. Thanks. Best. K7
View Replies !
Sharepoint Integration With Erroneous Date Format In Date Time Picker
Dear Expert! A server with SQL 2005 sp2, Reporting Services and Sharepoint services (ver 3.0) (in integrated mode) gives an odd error. When viewing a Reporting Services report with a Date Time Picker, the date chosen is wrong. The preferred setting is Danish with the date format dd-mm-yyyy. The date picker shows the months in Danish but when selecting a date, and clicking on the Apply-button, the date reformats to US (mm-dd-yyyy). Example: When choosing 5th of September 2007 and clicking apply, it shows in the picker, 9th of May 2007. When choosing 26th of September 2007 and clicking apply, it shows, again in US format, the RIGHT date but adds a timestamp €œ12:00 AM€? in the end, making further enquiries to fail. The report itself receives the right date and shows correctly. The only case it fails is, when the time stamp appears. The server is a 32-bit one with 4 GB RAM. A testserver with identical collation on the Reportserver database cannot recreate the error. The site containing the reports has been set to Danish in the regional settings. To Reinstall is not an option. The test report has no database connection whatsoever. When setting the site to US, the timestamp won€™t appear at all. The server has been restarted and the installation procedure was of the simple kind. No special tweaks at all. Any advice would be greatly appreciated. Kind Regards Johan Rastenberger
View Replies !
First Time Installing - Can't Find BIDS
Thank you to all that take the time to read this! All software is installed on a single development pc. I have a SQL Server 2000 Developer Edition Database that I am using to test a new Accounting System. SQL Server 2000 is the required format. I would like to start developing some SRSS reports against that database. It was suggested that I install the SQL Server 2005 Reporting Services, because I was told 2005 is less buggy and easier to use. I installed SRSS 2005, and only SRSS 2005, and used the Configuration Tool to get it configured properly, I think. I have green checks on everything except "Email Settings" and "Execution Account" Now, I want to write some reports. BIDS is not under the "Microsoft SQL Server 2005" program group, only "Configuration Tools". I was also told that I could use Visual Studio to write reports. I installed Visual Studio 2005 Standard Edition. When I go start a new project, "Business Intelligence€? is not a project type or sub-type. What do I need to do so I can either install BIDS or use VS 2005 to write reports?
View Replies !
Find Time Ranges Records
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this? Kamran Shahid Sr. Software Engineer(MCSD.Net) www.netprosys.com
View Replies !
Script To Find Last Time Cubes Get Processed
Hi Guru,Where can I find OLAP tables Metadata?I want to query metadata to find out when was last time the cube ran? Ischedule the cube to process every night. If the cube did not getprocessed then I'll get e-mail.Please provide some tables where I can track down the last time thecubes get processed.Thanks so much,Silaphet,
View Replies !
Query To Find SQL Activity At Specific Time?
Ok, please pardon my complete ignorance; but I am extremely SQL challenged. I manage the server right now and I am the closest thing to a DBA at the moment with this server. Here is the problem. I have a job that runs every hour and until last week it was running without any problems. Now, once a day it hangs, and I end up having to restart sql to get it to be functional again. I guess I could just kill the spid, but regardless, it's fails. It doesn't happen at the same time each day. I thought it was from the backups doing a SQL backup job, but I eliminated that possibility. I can't find what is causing the problem, because the SQL logs just stop at the time that this happens. It's like some other job/query is running and taking all the sql server resources. But I can never catch it when it's happening and I have no event logs or SQL logs to go on. Is there any query that I can run to find what happened around the specific time the job failed to run? I just need to find what process did what when this job stopped working. Again, I apologize if my lack of SQL knowledge is making me ask dumb questions, but I am really lost. Thanks in advanced!
View Replies !
Putting Date Or Date && Time In A Column Thats Going To Be Heavily Used?
Hello, We have a bunch of Audit tables that contain almost exact copies of the operations tables. The audit tables also include: AuditID - the audit action (insert, modify - old, modify - new, deleted) AuditDate - date and time of action AuditUser - User who did it... At the end of the day I need to know for any given record what it looked like at the beginning of the day and what it looks like at the end of the day. There could have been numerous changes to the record throughout the day, those records I am not interested in. Only the first record and the last record of a give day. I am going to be doing a lot of MIN(AuditDate) and MAX(AuditDATE) and .. WHERE AuditDate BETWEEN '10/1/2007 00:00:00' AND '10/1/2007 11:59:59' ... Question: Whats better for performance: 1. Separating out the date and time and doing a clusterd index on the date. 2. Keeping date and time in the same column and just use a normal index. 3. Better ideas? Thanks, Bradley
View Replies !
Problem With Current Date For Date & Time Field
I have a table named "shift" and I need to setup my query to return only data where the field "startime" = today. The problem I am running into is the starttime field it laid out like "2005-12-29 14:00:00" with different time values. I need to ruturn everything that has todays date regardless of the time value. I tried using GetDate() but that is returning data for other days as well or just data before or after the current time. Does anyone have any suggestions? This is driving me crazy! Thanks, Garrett
View Replies !
First Time User That Can't Find Answer. Need Online Website.
HI! Thank you for even looking at this question. I need to get a website up and running for a small group of people. I've had SQL Server for a while, but didn't have any use for it. It came with Visual Studio 2005, which gave me a little know how on scripting. Now, I have Visual Web Developer and what I need to make a web page. I just need to know how to configure all my settings to allow my website to be viewed online by anyone with an internet connection. I'm a person that understands detailed or simple directions, but I can not seem to find directions online. Now, I know what your thinking, but I've never attemped anything like this before. So, please, HELP! I need and want to learn as much as possible. Thank you, tecfreak213 P.S. also, how do I change or get a web address? Thanks.
View Replies !
Date Time Format For Date Parameter
Hello, I am using the calender parameter and I need to convert my data date format to the one that matched that is returned on selecting a date from this calender. Can you show me what this format is. how can I convert my existing date format to this format. The existing date format is 2007-07-26 21:27:13.000 thank you Kiran
View Replies !
Find Phonecall Records Based On User At The Time Of Call
i have a table of phonecall records detailing amongst other things the time and duration of the call. there is a relationship linking this table to a table of handsets and from there to a table of users. this users table lists the start date the user had the phone. as ever sample data might make this easier to show: PhoneUsers table PhoneUserID Name StartDate PhoneID 1 john 1/08/2006 1 2 bob 20/08/2006 1 3 fred 2/08/2006 2 etc Phones table: PhoneID 1 2 etc PhoneCalls table: PhoneCallID PhoneID CallDate Duration 1 1 10/08/2006 25 2 1 23/082006 20 3 2 23/08/2006 20 i want the following result set: user calldate duration john 10/08/2006 25 bob 23/08/2006 20 fred 23/08/2006 20 essentially i am trying to link the phonecalls to the user of the phone at the time of the call. i make the following assumptions regarding the data: the user is responsible for all calls for a given handset from the 'startdate' until the next 'startdate' for a different user, if such a record exists, if not then for all calls after that date thanks
View Replies !
Problem With Getdate() In Transaction Takes The Insert Time Instead Of The Commit Time
Hi, We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table. If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update. We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp. We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below: /* Different functions to get current timestamp €“ all have been tested to produce the same results */ /* SELECT GETDATE() GO SELECT CURRENT_TIMESTAMP GO SELECT {fn Now()} GO */ /* Use these statements to delete the tables to allow recreate of the tables */ /* DROP TABLE COMMIT_TEST DROP TABLE COMMIT_TEST_UPDATE */ /* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */ CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */ GO CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */ GO /* Use these statements to delete the triggers to allow reinsert */ /* drop trigger LOG_COMMIT_TEST_INSERT drop trigger LOG_COMMIT_TEST_UPDATE drop trigger LOG_COMMIT_TEST_DELETE */ /* Create insert, update and delete triggers */ create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as begin declare @time datetime select @time = getdate() insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE) select PKEY, getdate() from inserted end GO create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as begin declare @time datetime select @time = getdate() update COMMIT_TEST_UPDATE set LAST_UPDATE = getdate() from COMMIT_TEST_UPDATE, deleted, inserted where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY end GO /* In our application deletes should never occur so we don€™t log when they get modified we just delete them from the UPDATE table */ create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as begin if ( select count(*) from deleted ) > 0 begin delete COMMIT_TEST_UPDATE from COMMIT_TEST_UPDATE, deleted where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY end end GO /* Delete any previous inserted record to avoid errors when inserting */ DELETE COMMIT_TEST WHERE PKEY = 1 GO /* What is the current date/time */ SELECT GETDATE() GO BEGIN TRANSACTION GO /* Insert a record into the primary table */ INSERT COMMIT_TEST (PKEY) VALUES (1) GO /* Simulate additional processing within this transaction */ WAITFOR DELAY '00:00:10' GO /* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */ COMMIT TRANSACTION GO /* get the current date to show us what date/time should have been committed to the database */ SELECT GETDATE() GO /* Select results from the table €“ we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */ /* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */ SELECT * FROM COMMIT_TEST GO SELECT * FROM COMMIT_TEST_UPDATE Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window). Regards, Mark
View Replies !
It Takes A Long Time To Insert The First Record Each Time When The Program Start
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz. The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it? I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action. cn.Open() sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table scb = New SqlCeCommandBuilder(sda) sda.Update(dataset) cn.Close() I check the sda.update(), it takes about 0.08s for filling one record into database normally. But: 1. Start the PPC Program 2. Load DB into dataset 3. Create a ONE new record in dataset 4. Fill back to DB When I take this four steps everytime, the filling time is almost 1s or even more! Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key) However, when I give up the dataset and using the following code: cn.Open() Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field) cmd.CommandType = CommandType.Text cmd.ExecuteNonQuery() cn.Close() StartTime = Environment.TickCount I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
View Replies !
SQL TIME AND DATE
Hi I have created a simple SQL database with four columns using Microsoft SQL Server Mangement Studio Express - (Name(varchar), Email (varchar), Comment (varchar), Datetime). The idea is for this database to hold feedback and comments from my website by connecting a form view with insert capabilities to this database. How can I configure the Datetime column in the database to automaticaly input the time and date from when the user posted the comment? Thanks
View Replies !
Date But Not Time
I would like to know how to do a select on a datetime field that will return to me only the Date but not the time. Please help out. Thanks. I ran through all the built in date functions and couldn't find anything for this task. I still can't fathom why there isn't a date function for this simple but a must have option. blumonde
View Replies !
Date/time Or Date And Time
I am setting up an SQL database and I will need to get differences in dates. For example I have a start date, start time, completion date and completion time and I want to get the difference between the start and completion. Would it be better to have one field with both date and time in it, or better to have a date field and a time field? Even though I have already started setting up the tables with seperate fields for date and time I am now leaning toward one field with date/time in it. (Only because that is the way I had to do it when setting up an Excel spreadsheet for a similar task) When that is decided could someone please point me to a good resource for explaining to me the iConvertible method. I tried a simple asp.net page to insert a record into the database and got an error method telling me I had to use the iConvertible method. (I am programming in C#). I use a textbox on a webform to input the date and time information and the SQL database fields are set up as date/time. I looked at the visual studio documentation but that doesn't help me much. It doesn't show me the syntax required and how to "use" the method. Thanks
View Replies !
Date/time Bug?
Hi I've just noticed something unusual happening when entering into a field with a date/time data type. When entering 31/06/2005 it displays a message saying:- The value you entered is not consistent with the data type or length of the column. But if I enter 31/06/05, it changes the value to 05/06/1931. Is this a known problem with SQL Server? I did a search on google, but nothing came up. Thanks
View Replies !
How Tell Sql To Consider A Value As Date Time
hi as you know when you want to tell sql to conside a value as STRING, put a " around the value. i want it for date value, somethin that works like this for me: CONVERT(datetime,'2003-06-07'). in some languages # around the values works, but in sql?
View Replies !
Next Run Date And Time
Does anyone know how to determine the next run date and time of a scheduled job while the job is running? I have found that using DMO or SPs to find this information while a job is running returns the time the job started. I am not sure but I believe that SQL Server calculates the next run time after the job has been completed if anyone knows to figure this out while a job is running I would greatly appreciate it. Thanks Keith
View Replies !
Get Rid Of The Time In A Date
Hi all! When I cast a date as an integer and then cast it back as datetime I still get a date with time as 00:00:00.00. How can I get rid of this time and just have a date? Thanks, DBA
View Replies !
Date Time
Hi all, My company has just moved from using an Access database to SQL server for the company web site. I am new to using SQL server and I was wondering if there was a way to get SQL server to accept dates in dd/mm/yy format as I get an error message when I try to insert any data via my web site which this format. I don't want to change my regional setting on the server to mm/dd/yy unless it is the only option as it would involve modifying code on the site and on other components running on the server? Thanks in advance for any help. George
View Replies !
Date Time
I have a table which has a datetime column.There might be rows for every second. I am trying to get the count of rows for a certain day. How can I do that? and How can I format the column to get only date and not time part of the column value. Thanks, Khalid
View Replies !
Date Time Help
I have a two fields one called Date and another called time. I would liked to combine these fields and then create another field that would just reflect the time. Here is and ex. Field-DATE - 8/20/1967 Field - TIME-11:50 I would like to do this: Field-DATE-11/20/1967 115025 Field-TIME - 11:50 Can someone tell me how can I do this. Again I just want to combine the date and time field then create a field that will only reflect the time. But the time must be the same as what was combined. If you need further clarification feel free to just ask.
View Replies !
Date Time
I get the following error when using datetime. Msg 4145, Level 15, State 1, Procedure rpt_GetAllOutstandingVouchers, Line 32 An expression of non-boolean type specified in a context where a condition is expected, near 'AND'. CREATE procedure rpt_GetAllOutstandingVouchers @cmb1 as varchar(100), @dtm1 AS DATETIME @dtm2 AS DATETIME AS BEGIN SELECT DISTINCT Site.siteName, payment.paydate, payment.notes, payment.amount, payment.voucher, payment.processed, CASE WHEN processed = 0 THEN 'NO' ELSE 'YES' END AS Redeemed FROM payment INNER JOIN Site ON dbo.payment.siteID = dbo.Site.siteID WHERE voucher = 1 AND cast(floor(cast(@dtm1 as float)) as datetime) AND dateadd(minute,-1,dateadd(day,1,cast(floor(cast(@dtm2 as float))as datetime))) AND @cmb1 = Site.siteName GROUP BY Site.siteName, payment.paydate, payment.notes, payment.amount, payment.voucher, payment.processed END
View Replies !
Date Time
Hello all I am having a hard time in solving this My application gives me a date in the format Ex: Tue Oct 16 13:23:04 CDT 2007 coming to Oracle db as a string This date(String) is going to SQL Server into three differnt Colunm Col1 will have : 2007 - 10 - 16 Col2 will have : 1:23:04 (Military time has been changed to 12 hr format) Col3 will have : PM (Since its was 13 hour it PM) The format I am getting from application is giving me trouble. How can I do this? Please advice!
View Replies !
DATE TIME
IN THIS QUERY SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY] O/P July 04, 2006 RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) PLEASE DISCRIBE ME WHAT HAPPEN IN THAT PLACE spatle
View Replies !
|
TRACKER
