Hey All,
I am new to programming in SQL developer and I was hoping one of you kind salmon of knowledge could help me
I am running an SQL script every 15 minutes to pull data. I would like to insert the results into a column in a table. I have two issues:
1.How can I give the result column the current time stamp as an alias?
2.How can I add this column to the results table (I know its Alter table but do I put this into the insert sql?)
Hi,I am having a problem with aquery. Firstly here is a script to createthe table and insert some sample data:CREATE TABLE [dbo].[tbltemp999] ([Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[EventDate] [datetime] NOT NULL ,[EventID] [int] NOT NULL) ON [PRIMARY]GOINSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:05:14.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:08:32.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:24:45.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:28:25.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:53:38.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:57:02.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:09:10.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:12:48.000',6005)The EventIDs 6005s represents a system start up and an EventID 6006represents a system shut down/restart. I already have a query tocalculate the downtime of a particular date range. So when i want tofind the total uptime of a date range in the past i do the following:Date1 00:00:00.000 to Date2 23:59:59.999 then subtract the downtime.My problem arises when Date2 is todays date as the time of23:59:59.999 would not have reached so the calculations are alwayswrong.How can I make the Date2 also read the current time?If this is vague info feel free to ask me to explain better.ThanksSunny
Hi ALL, I have something that i have been wondering about for a while now and hope someone can help clear this up for me. My mdf and ldf files on my server have a time stamp from 2 days however the database is in use 10 hours a day including backup time. Is there any reason for this the OS is XP SP2 and SQL 2000 MSDE. Hope some one can help on this TA Stumblin
I am pretty new at this so please bare with me.I have a page working perfectly but I just want to ad a column that willshow the time it was entered into the database. I think all I need is thesyntax for writing the server time to the TimeStamp field in the record. Ihave hidden field in the form that I alocated. So how do I get it to writethe current time to the database. Here is the current code:<form name="form1" method="POST" action="<%=MM_editAction%>"><table width="500" cellpadding="2" cellspacing="0"><tr><td width="242" class="tdtl">Are you coming to the lake this weekend?</td><td width="244" class="tdtr"><input name="Answer" type="radio"value="Yes" checked>Yes <input name="Answer" type="radio"value="No">No</td></tr><tr><td class="tdleft">Your Name Please </td><td class="tdright"><input name="Name" type="text" id="Name"size="40"></td></tr><tr><td class="tdleft">I will be arriving on . . . </td><td class="tdright"><select name="Arriving" size="1" id="Arriving"><option value="Cant make it">Cant make it</option><option value="Friday">Friday</option><option value="Saturday">Saturday</option><option value="Sunday">Sunday</option><option value="Monday">Monday</option><option value="Tuesday">Tuesday</option><option value="Wednesday">Wednesday</option><option value="Thursday">Thursday</option></select></td></tr><tr><td class="tdleft">Boathouse</td><td class="tdright"><input name="BoatHouse" type="text" id="BoatHouse"size="6" maxlength="6"> </td></tr><tr align="center"><td colspan="2" class="tdleft"><input name="Submit" type="submit"onClick="MM_validateForm('Name','','R');return document.MM_returnValue"value="Submit"></td></tr></table><input type="hidden" name="MM_insert" value="form1"><span class="tdright"><input name="TimeStamp" type="hidden" id="TimeStamp"value="<%=(rsList.Fields.Item("TimeStamp").value)%>"></span></form>Is it this last "Input" statement that I need to change to get this to workright?ThanksHouston
Escalations come in at any time and need to be tracked. If the escalation gets hot enough there is a need to track this as a "Hot Site" by the account name. Once tracked under the account name, the account may have numerous escalations. Additionally, it is possible to open an account and not yet have any escalations.
My problem is that I need to track on the account level when the last change was made, so I would need to find the MAX date of either the tblAccount.lastUpdate or any of the related tblEscalation.lastUpdate fields. Is there a preferred way to do this?
In the last iteration I just performed a getdate() on either set of records and have a grotesque query that pulls the max date, but it only works if both tables have related data. Maybe just my query is bad, but seems like I'm missing something obvious here.
I also need to track the engineer who made the last change.
When a record is written to a table (via a asp form), I'd like the timeand date from the server to automatically populate a column in thattable. From what I can tell, timestamp isn't working. I rather nothave the time come from the client.Thanks for the help.
I am moving the data from a flat file to a table in SQL Server 2005. One of the field in the table is time stamp which tells me when the data is loaded. Which is best way to populate this field (time stamp) with constant time stamp for the entire batch data? Thank you, SQL Server 2005 SP2.
Is there a property setting in SqlEX. That automatically inserts the date and time in to a field (timestamp) in the dB, when a record is created. If so can someone please show me how this is done.
I create back end table on SQL Server and front end in Access. On my sql table I select the datatype as date/time and on the default value on the field I put this one (dateadd(day,datediff(day,0,getdate()),0). When I input the data in to a SQL table and refresh its showing correct as 12/06/2006. But when I checked in access its showing the date data as 2006-12-06 00: but I want to show 12/06/2006. Can you tell what I have to do SQL table default field to come out like 12/06/2006 not 2006-12-06 00:. Thanks and appreciate your comments.
I have a shopping cart that will get full from time to time becausecustomers click out of the site before they confirm their purchase,therefore leaving a full cart behind. I'd like to have a timestamp onthis table, so that I can delete anything that I find is more than a dayold.How can I do this?Thanks,Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
How do I get the file name appended with the time stamp info like this: Order_file__2002626_8_34_4.csv in an automated process which generates the file? Any help??
I have a table (tbl_entries) in my db that has a timestamp field (startDate). What I need to do is run a query that counts the number of records on a 15 min interval.
something like
start_date 2008-01-01 00:00:00.000 2008-01-01 00:00:00.000 2008-01-01 00:00:00.000 2008-01-01 00:01:00.000 2008-01-01 00:01:00.000 2008-01-01 00:01:00.000 2008-01-01 00:14:00.000 EVERY THING ABOVE HERE IS IN GROUP 1 2008-01-01 00:35:00.000 EVERY THING ABOVE HERE IS IN GROUP 2 2008-01-01 01:01:00.000 2008-01-01 01:03:00.000 2008-01-01 01:03:00.000 2008-01-01 01:04:00.000 EVERY THING ABOVE HERE IS IN GROUP 3 2008-01-01 01:29:00.000 EVERY THING ABOVE HERE IS IN GROUP 4 2008-01-01 01:41:00.000 EVERY THING ABOVE HERE IS IN GROUP 5 2008-01-01 02:25:00.000 2008-01-01 02:28:00.000 2008-01-01 02:31:00.000 2008-01-01 02:33:00.000 EVERY THING ABOVE HERE IS IN GROUP 6 Hope this is enough Info
--main table > create table test (manufacturer varchar(500), score int) > insert into test values('Toyota', 1 ) > insert into test values('Toyota', 2) > > --logging table > > create table log_old_val(manufacturer varchar(500), score int, operation > varchar(10)) > > --trigger to log old value into log_old_val table. > create trigger tr_man on test > for update,delete > as > if @@rowcount = 0 > return > if exists (select * from inserted) > if exists (select * from deleted) > insert into log_old_val > select manufacturer,score, 'update' from deleted > > if exists (select * from deleted) > if not exists (select * from inserted) > insert into log_old_val > select manufacturer,score, 'delete' from deleted > go
Hi - I am a bit of an amatuer, but I am trying to use the EMS data Pump to get all of the data out of an Interbase 6 DB into an MSDE DB. I have managed to instal an Interbase ODBC driver, and I ca connect to it and then automatically create the tables in my MSDE DB, but I get an error stating that there cannot be multiple Timestamp columns in the MSDE databse. Is this true ?
I'm pretty new at this, writing SQL and reporting services. I created a report with a date parameter. I need the report to ignore the timestamp. My @Startdate is fine because the timestamps is at 12:00:00AM but my @EndDate also has this timestamp. I need to pull all the data up to the end date the user enters without taking the timestamp into consideration.
If someone can help me out with, I would greatly appreciate it.
I would like to make a function to convert a datetime to half-hour. E.g. If the timestamp is 1:23:05 then converts to 1:30:00, if 1:35:27 then converts to 2:00:00.
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)
I would like to know as to how can I put a Current Date & Time stamp on a FILE NAME automatically which is created by a DTS package.
E.g. I create a file named ABC.TXT daily. How do I get this file to have the current Date stamp so that the file name is ABC121201.TXT without any user intervention.
I need to separate the date stamp (which looks like this 2006-10-05 09:08:41.000) into the date in this format 05OCT2006 and then the date stamp separate in this format 09:08. Thanks!
The following question might sound a bit stupid but I'm not a database expert so hopefully nobody minds me asking it.
Here's what I did:
1. I created an SSIS package that is supposed to import new data into my data warehouse as it becomes available.
2. Since I need to maintain some of the history I use the Slow Moving Dimensions part (set the history flag on input fields) but run into an error condition while running the package. The message basically says that I'm about to create a duplicate record which is not allowed.
Now on some records the package is supposed to archive history by populating the (new) fields. In order to keep the record unique (primary key constraint) thought, do I need to make the (new) fields primary keys as well?
So I guess I'm struggling with a more basic concept;)
I would appreciate if somebody could shed some light on this.
SQL Express 2005 with one instance on a networked dedicated stand-alone window XP€¦ We use this for custom software written in VB.net. We are not SQL experts€¦ Question 1) I am getting a event viewer warning that says The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs. This seems strange, since there is only one CPU on this machine. Any ideas? Question 2) In SQL Server Management Studio Express, we do not have the actual data files attached to the instance, but the custom program does use the actual data files just fine, defined by the connection string. Is there any pro/con to having them attached in SQL Server Management Studio Express? Thanks! Bob
ALTER procedure [dbo].[MyPro](@StartRowIndex int,@MaximumRows int) As Begin Declare @Sel Nvarchar(2000)set @Sel=N'Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM Between ' + convert(nvarchar(15),@StartRowIndex) + ' and ('+ convert(nvarchar(15),@StartRowIndex) + '+' + convert(nvarchar(15),@MaximumRows) + ')-1' print @Sel Exec Sp_executesql @Sel End
--Execute Mypro 1,4 --->>Here I Executed Error Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM Between 1 and (1+4)-1 Msg 207, Level 16, State 1, Line 1 Invalid column name 'ROWNUM'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'ROWNUM Procedure successfully created but giving error while Excuting'. Please anybody give reply Thanks
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
