I have a table with a [Timestamp] field which is a datetime data type. What I need to do is add one hour to the timestamp.
Each entry in the [Timestamp] field looks like this: 2008-03-09 16:44:06.313
What is the best way to do this?
(Should I be using UPDATE [Tablename] or Alter Table [Tablename])
For starters, please feel free to move this if it is in the wrong forum.
The issue I have is this. I have been asked to delete all information from a table that was inserted before May 12 this year. The issue is that when the DB was created, whoever designedd it neglected to add a timestamp column for the user data table (the one I need to purge). Does SQL, by default, happen to store insert times? Would it be something that might hide ina log file somewhere?
I have a Date column in my table.I want to insert the Date and hour in that Column.
Here is the sample data 2011/11/25 11 AM
The Text in Bold i.e 11 Am is how the hour part i want.
I just need the date, hour, and minute...not the micro seconds. Do I have to DATEPART and concatenate each or is there any way to simply truncate the milliseconds from it? Or is there a date format to put extract and report on it as...
MM/DD/CCYY HH:MM:SS AM
I see there is a format 131 that puts it in..
DD/MM/YYYY HH:MM:SS:MMMAM
I have a table that is recording hits to a website. Everytime someone views a page, the datetime of the hit is recorded in a field called hit_date_time. I would like to be able to come up with a query that will show how many hits occured on a given day or given days, broken down by hour.
The resulting table for two days would look something like:
Time Hits
1/1/01 12:00 1
1/1/01 1:00 23
1/1/01 2:00 54
1/2/01 1:00 15
1/2/01 2:00 14
I can't seem to figure out how to write the query so that I can take into consideration the date and hour of the event so that I can count it.
Thanks,
Eron
Hi!
What correct statement should be to convert timestamp column to datetime.
I wrote:
select convert(datetime, update_date)
from table_name
and it doesn't work properly.
Thank you,
Elena
ASP.NET application, MS SQL DB, and a table with a timestamp(8) column.
Error:
Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column.
I'm using an SQLDataAdapter and typed DataSet, inserting a row into the dataset, and calling Update() to send changes to the DB.
The thing that baffles me about receiving this error, is that the DB column can be null and I get this error wheter I attempt to insert NULL or a valid byte array into the column.
(No - I am not trying to insert a datetime into the column.)
The error just doesn't seem too descriptive of the problem I'm having, and I'm quite confused.
All help greatly appreciated!
Slezak
I have a question regarding the rowversion / timestamp column. I want to use it to track changes in some tables and I created a non clustered index on the timestamp column (with three includes) to get a better performance while searching / gathering the data. It works very well, but I experienced a big performance hit on the inserts and my first thoughts are that it has something to do with this index.
I know that this NCI has to be updated with every insert and so this could have an impact on performance, but we have a lot of other indexes as well, which are also get updated with every insert. Why this index / column has such a big impact? Our application use a lot of dynamic cursors, maybe it has something to do with that?
I can not insert CURRENT_TIMESTAMP into column type timestamp
I defined a data Table that has column type timestamp.
I did not achieve insert CURRENT_TIMESTAMP data into column typed timestamp.
My statement is below.What is wrong with it? Thanks
set @cmdS2 = 'Insert Into TABLOLAR Values(' + CHAR(39) + @TABLO + CHAR(39) + ',' + CHAR(39) + @TABLO_ACIKLAMASI + CHAR(39) + ',' + CHAR(39) + CURRENT_TIMESTAMP + CHAR(39) + ',' + CHAR(39) + @KLLNC + CHAR(39) + ')'
I have been provided with a table where one of the columns is of TimeStamp data type. My question is how to insert and update data in this column through my SQL Statement? When I run my SQL statement, it gives me an error with this column name in the error.
View 3 Replies View RelatedIm trying to insert the values from this query into a table, so I can later check the history of memory usage:
SELECT
[total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]
What I'm missing is a way to insert the current timestamp every time I insert to the table.My plan is to use the insert into command.
Hi Friends
I have transactional replication,
The publisher DB contains table call Courser with timestamp column, this column values are unique for the publisher DB
The subscriber DB also contains same copy of data in publisher DB Course table, but the timestamp column values are different.
So my problem is how can I keep this two tables (Course) identically, (same timestamp column vales in both table)
NOTE: Publisher and Subscriber DB reside under two different SQL server instance
Thanks and regards
IndikaD (Virtusa cop SL)
I am using SSMA 6.0 for DB2. When trying to migrate data with a table have timestamp column, it fails with an error "Hour, Minute, and Second parameters describe an unrepresentable DateTime." however i don't see any issues with the source data.
View 5 Replies View RelatedI have a table that looks like this:
[Timestamp] [P]
2008-04-06 17:30:00 119
2008-04-06 17:30:03 120
2008-04-06 17:30:07 119
2008-04-06 17:30:11 118
2008-04-06 17:30:12 129
2008-04-06 17:30:13 125
What I'm after is to create a new [dtP] column where the [dtP] is the [P] value at least 5 seconds later. I would assume I should use the dateadd() function but I've tried [Timestamp] = dateadd(ss,5,[Timestamp]) in my WHERE clause and to no avail. Any suggestions on the best way to proceed?
Desired Result:
[Timestamp] [P] [dtP]
2008-04-06 17:30:00 119 120
2008-04-06 17:30:03 120 121
2008-04-06 17:30:07 121 122
2008-04-06 17:30:11 122 125
2008-04-06 17:30:12 128 125
2008-04-06 17:30:13 129 etc...
2008-04-06 17:30:14 125
We're trying to perform an upgrade test against a copy (backup/restore) of our customer database as target. There are some tables with timestamp column in the database. The way we do this is by having a database project with a publish profile targeting that copy of customer database and then with TFS build server is used to build the database but only to generate a publish script (/p:UpdateDatabase=False) set in the build definition - msbuild argument.
Example of table definition:
CREATE TABLE dbo.CodeTable1
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,Code CHAR(6)
,[Timestamp] TIMESTAMP NULL);
We would like to have the "Code" column to have CHAR(7), so in the project we modify the table definition:
CREATE TABLE dbo.CodeTable1
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,Code CHAR(7)
,[Timestamp] TIMESTAMP NULL);
Expecting SSDT build will generate alter script:
ALTER TABLE dbo.CodeTable1 ALTER COLUMN Code CHAR(7);
To our surprise the generated script was:
ALTER TABLE dbo.CodeTable1 ALTER COLUMN Code CHAR(7);
ALTER TABLE dbo.CodeTable1 ALTER COLUMN [Timestamp] TIMESTAMP NULL;
Which will cause error when the script is executed: "Cannot alter column 'TIMESTAMP' to be data type timestamp."
Why is SSDT generating the change script for that timestamp column??
We then try a local build in VS, the issue is not happening, SSDT correctly generates alter script only for the "Code" column to CHAR(7);
Both local machine and TFS Build server are having VS 2013 Update 4- SSDT 12.0.50318.0 installed.
As we tried to troubleshoot further, we found out that it seems it only happens on a restored database from a backup copy of our customer database. It doesn't happen for databases created by SSDT build from scratch or that we manually created. We've tried make sure all database properties are the same as the database that correctly built.
But still if the target database is the one we restored from a customer's copy, SSDT always tries to alter timestamp column (on server build).
--------------------------------------------------------------------------------
I would like to get information related to timestamp data type in SQL Server (WANT TO SET NULL IN TIMESTAMP COLUMN )I have Following case
try {
try {
stmt.execute("drop table timestampTable");
}
catch (SQLException ex1) {
}
stmt.execute(
"Create table timestampTable(c1 int Primary Key, c2 Timestamp)");
PreparedStatement pst = connection.prepareStatement(
"insert into timestampTable values(?,?)");
pst.setInt(1, 2);
pst.setNull(2, Types.TIMESTAMP);
pst.execute();
}
catch (SQLException ex) {
ex.printStackTrace();
}
TRACE IS GIVEN BELOW
====================
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Disallowed implicit conversion from data type datetime to data type timestamp, table 'ClientDB.dbo.timestampTable', column 'c2'. Use the CONVERT function to run this query.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeInter nal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec ute(Unknown Source)
at JDBC.TestSQLServer.testTIMETAMPDataTypes(TestSQLSe rver.java:75)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.daffodilwoods.tools.testworker.TestRunner.runM ethod(TestRunner.java:159)
at com.daffodilwoods.tools.testworker.TestRunner.runI nSequence(TestRunner.java:83)
at com.daffodilwoods.tools.testworker.TestRunner.runT estCase(TestRunner.java:4
PLEASE REPLY ME AS SOON AS POSSIBLE
THANKS
SUBE SINGH
After several hours of trying, I trow the towel in the ring and come here to ask a question.
Source system uses a timestamp column in the transaction tables. which is equal to a non-nullable binary(8) datatype (sql 2000 bol).
What I want to do is get the timestamp at the start of the transfer and at the end of the transfer of data. and store these in a controltable
I try to do this in 2 sql execute tasks:
sqltask 1: "select @@DBTS AS SourceTimestamp" and map the resultset to a variable. Here come's the first problem what variable type to take ?
DBNULL works (meaning it doesn't give errors) (BTW: is there a way to put a variable as a watch when debugging sql tasks ?)
INT64 and UINT64 don't work error message that types for column and parameter are different
STRING works
Then I want to store this variable back in a table of a different data source
sqltask2: "insert into controltable values(getdate(), ?)" and make an input parameter that takes the previous timestamp ...
if I took DBNULL as a type for the variable there doesn't seem to be a single parameter type that works ???
if i take STRING as a type for the variable I have to modify the sql to do the explicit conversion from string to binary so I change CAST(? as binary). It doesn't return any error but the value stored in the table is 0x00000000000 and not the actual timestamp.
Any help on this one ? Why are the INT64/Bigint not working here, you can perfectly do a convert(bigint, timestampfield) in sql ?
How came the SQL datatypes, and the variable datatypes, parameter datatypes are so badly alligned to each other (and all seem to use different names) ?
tx for any help
Dirk
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
I cannot manage to fetch the new timestamp value inside a TSQL Transaction. I have tried to Select "@LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match. If they do, the Update begins as a Transaction. I send @LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@NewLastChanged"): 1 select @TimestampCheck = LastChanged from ADD_Address where AddressId=@AddressId
2
3 if @TimestampCheck is null
4 begin
5 RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1) -- AddressId does not exist.
6 return -1
7 end
8 else if @TimestampCheck <> @LastChanged
9 begin
10 RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1)
11 return -1
12 end
13
14
15 Begin Tran Address
16
17 Update ADD_Address
18 set StreetNumber= @StreetNumber, AddressLine1=@AddressLine1, StreetTypeId=@StreetTypeId, AddressLine2=@AddressLine2, AddressLine3=@AddressLine3, CityId=@CityId, StateProvidenceId=@StateProvidenceId, ZipCode=@ZipCode, CreateId=@CreateId, CreateDate=@CreateDate
19 where AddressId= @AddressId
20
21 select @error_code = @@ERROR, @AddressId= scope_identity()
22
23 if @error_code = 0
24 begin
25 commit tran Address
26
27 select @LastChanged = LastChanged
28 from ADD_Address
29 where AddressId = @AddressId
30
31 if @LastChanged is null
32 begin
33 RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1)
34 return -1
35 end
36 if @LastChanged = @TimestampCheck
37 begin
38 RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1)
39 return -1
40 end
41 return 0I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?Thank you,jspurlin
How to get the details of a stored proc or sql query which updated a particular table for specified time stamp or interval. Is there any query to get this?
View 3 Replies View RelatedHi
SSIS is brand new for me.. Playing with since a few hours..
Iam trying to import a Flat File into the SQLSERV DB using SSIS..
One of the column is in this format -- "YYYYMMDDHH24MISS"
How do i get around this to import the data in a readable fashion into the Destination?
Thanks!
MKR
date time s-sitename TimeTaken(Seconds)
6/8/2015 10:56:26 TestSite 100
6/8/2015 10:56:26 TestSite 500
6/8/2015 10:56:26 TestSite 800
6/9/2015 11:56:26 TestSite 700
6/9/2015 11:56:26 TestSite 200
6/12/2015 12:56:26 TestSite 700
I have a table with above values, I am looking for a sql query to find AvgTimeTaken at different time stamps and total count of each time stamp
Output
date time s-sitename TimeTaken(Seconds) Count_of_Request
6/8/2015 10:56:26 TestSite 1400 3
6/9/2015 11:56:26 TestSite 900 2
6/12/2015 12:56:26 TestSite 700 1
Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?
The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.
convert(char(15), process_date,112) as process_dt
When updating a timestamp column value to null from within a java program, I get the exception below. The env is MS SQL server 2005 SP2, and MS jdbc driver version: 1.1.1501.101. Please advise of any workarounds or solutions.
Update <table_name> set <colname_oftype_timestamp> to null
java.lang.NullPointerException
at com.microsoft.sqlserver.jdbc.AppDTVImpl$SetValueOp.executeDefault(Unknown Source)
at com.microsoft.sqlserver.jdbc.DTV.executeOp(Unknown Source)
at com.microsoft.sqlserver.jdbc.AppDTVImpl.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.DTV.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Parameter.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Parameter.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setNull(Unknown Source)
Thanks,
sm.
Hello people,
I'm migrating an application from asp to asp.net and access to ms sql 2005. On the old db, there's a table with a column to store date and a column to store time. Ms sql only works with date and time together, right? I created a query to get the time from time column and update the date column inserting the time. I did something like this:
UPDATE S_ACC_MONEYSET Date_Oper = CONVERT(varchar, Date_Oper, 101) + ' ' + CONVERT(varchar, Time_Oper, 108)
Does anyone knows a better way to do that? Any other comments?
Thanks!
I have about 23 SQL servers running 6.5 SP3 or SP5a in a 24 hour environment. Most of the activity takes place between 6am and 11pm, with few transaction after 11pm. What is best to do with the main DB's transaction log, have the truncate at checkpoint option checked OR back up the transaction log a couple times a week? The Database is backed up every 6-8 hours.
Thanks in advance for your opinions/help.
LN
Hi all,
We have many messages in SQL server logs that say 'Log backed up:Database:COM,creation date(time):2003/03/26(19:41:58),first'
why is this message appeared every one hour?
How to get hour from a date
Msg_Time = 10/28/2006 12:20:00 AM
here how to get hour, minuts
see for month we are using month(Msg_Time),
for year we are using year(Msg_Time) same way how to fetch the hour and minutes
all are appreciate
Hi All,
I have several servers each with an instance of SQL Server with a number of databases in each instance. We are a law firm dealing mostly with documents (Modifying and creating new). Is there an easy way to determin how many transactions the transaction log handles per hour? Not all tables have a create date or modify date. Is there some way to monitor the transaction logs them selves?
Thanks,
Roy
I have a pretty straight forward fact table that contains order information like this:
DateTimeStamp Price OrderID
I have a measure that does a simple row count for orders placed. How can I get the Max Orders Placed grouped by Hour? Something similar to this:
0:00 15
1:00 25
2:00 50
.....
23:00 75
I tried to do a max function like max([time].[hour], [measures].[Order Row Count]) but ended up with only one value. I know I'm doing it wrong.
I appreciate any help on this.
Hey guys,
I am doing a project that has start time and end time. i was given only the start time of cases, but not the end time.
for example:
start time
01:30:00
10:00:00
09:24:00
11:25:00
.
.
.
.
It was like, thousands of start time. I need to create an end time (smalldatetime) which is one hour passed the start time. can anyone tell me how to do it in sql query? thanks.
Jul
Hi
I'm trying to create a query, where I add one hour to a value (I marked it below with "+ 1 HOUR"). I can't find any informations, how I can do this.
Code Block
"SELECT field1, enddate, (enddate < GetDate() FROM table WHERE (enddate + 1 HOUR) < GetDate()"
Does anybody can help me with this problem?
Kind regards
Roland
Hi All,
I have an orders table which has a filed called OrderTime which is the exact time when the order was received ? I need to write the following queries
1. Get number of orders per year
2. Get number of orders per month (obviously if a month (for example, november) is in a different year, its to be in a different row)
3. Get number of orders per hour
Note: If there are no orders in a year etc, it should return a row with 0.