Column Alias Asthe Current Time Stamp
Oct 18, 2007
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?)
I would appreciate any help possible
Thanks
View 10 Replies
ADVERTISEMENT
Jul 20, 2005
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
View 1 Replies
View Related
Feb 13, 2007
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
View 3 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Dec 27, 2007
How do we put a time stamp on a report?
Thanks
View 1 Replies
View Related
Jan 8, 2007
Hi All,
How can I add the date/time stamp to the end of the text file? This file is being created as a result of query output of the bcp command:
'bcp "Select * from ##Test" queryout C:TestJobForDB.txt -S servername -U user -P password -c'
I need to add date/time stamp to the JobForDB.txt file before the period.
Thanks
View 14 Replies
View Related
Jan 16, 2007
Hi All,
I have a script that adds the date/time stamp to a file in the following format:
200701120149PM.
here is the script:
set dttm=%~t1
for /F "tokens=1-6 delims=/: " %%i in ("%dttm%") do (
set date=%%k%%i%%j%%l%%m
)
I need to display the time as military. How can I do that?
Thanks.
View 4 Replies
View Related
May 6, 2004
How can I know when a record on a table has been modified ?
I want to add a field and fill it with a date/time when the recors is modified
Thanks
View 14 Replies
View Related
Oct 27, 2006
Hello, first post here.
I am re-writing a web based application and trying to make improvements this go round.
Problem Statement:
I have two tables that need to work independently, but may be related at any given time.
tblAccount
accountID
accountName
accountNumber
lastUpdate
lastEng
etc...
tblEscalation
escID
fk_accountID
escNumber
escCustomer
description
notes
lastUpdate
lastEng
etc...
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.
Any ideas?
Thanks!
View 3 Replies
View Related
Jul 23, 2005
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.
View 2 Replies
View Related
Apr 23, 2008
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.
View 5 Replies
View Related
Apr 4, 2006
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.
Thanks in advance
View 1 Replies
View Related
Dec 6, 2006
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.
moetahsen
View 2 Replies
View Related
Jul 20, 2005
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!
View 1 Replies
View Related
Jul 31, 2002
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??
Thanks.
Reddy.
View 1 Replies
View Related
Apr 1, 2003
Hi all:
first of all, i must say that this website is just awesome...
my question is how do i truncate the time portion in a datetime stamp in a single sql statement.
thanks.
View 9 Replies
View Related
Mar 11, 2008
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
View 3 Replies
View Related
Jun 24, 2004
Down below is my tables.
If I want to add time stamp in log_old_val table.
what value in log_old_val and cone in trigger have to be modified?
thanks
************************************************** *****
--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
View 1 Replies
View Related
Oct 12, 2004
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 ?
View 3 Replies
View Related
Mar 7, 2007
Hi,
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.
Thanks,
View 4 Replies
View Related
Nov 16, 2007
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.
Anyone has any idea? Thanks.
View 4 Replies
View Related
Jun 8, 2008
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 14 Replies
View Related
Dec 13, 2001
Hi,
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.
Thanks for the help!
View 1 Replies
View Related
Jan 11, 2007
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!
View 2 Replies
View Related
Nov 14, 2007
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.
Original Table1:
PK1 field1
PK2 field2
PK3 field3
field4
field5
-----------
Now I enhanced it like this:
Extended Table1:
PK1 field1
PK2 field2
PK3 field3
field4
field5
CreateDate (new)
NewDate (new)
ActiveFlag (new)
_____________________
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.
Thanks in advance.
Dirk
View 3 Replies
View Related
Sep 25, 2007
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
View 4 Replies
View Related
Feb 23, 2008
Hi,
Get some errors in my log, does anyone knows how I can turn off all logging?
Indeed error should be investigated, but for the moment I just need to turn off all loggings.
Cheers!
Rickard
View 1 Replies
View Related
Feb 17, 2007
64 bit sql 2005 - SP2 CTP is creating several of this messages in the log. Could somebody tell me what is the impact of these on the performance?
Also, could anybody from MS have any idea as to the release date of SP2 ( Non CTP)
View 24 Replies
View Related
Feb 26, 2015
Is there a way to display a column alias as part of the result set column labels?
View 9 Replies
View Related
Jan 15, 2008
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
View 2 Replies
View Related
Dec 21, 2005
Manish writes "Hi there, any help will be greatly appreciated as i am struggling with this problem :)
I have a field in a database called time which also has the value time in it.
What i want to do is to have a sql query which will display all the records in that table where 5 mins have gone after that time."
View 4 Replies
View Related
Apr 9, 2008
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.
View 11 Replies
View Related
Apr 10, 2008
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 data
Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.
View 14 Replies
View Related
