|
Can anyone tell me how to change time for a DB in SQL Server 2005? I need to correct the time for a database, thanks.
|
|
|
|
|
You set the time of the windows server.
|
|
|
|
|
so you mean the database will use the time of windows server. my issue is for a specific database, say DB002, I wanna change its current time setting (set its current time 01-01-2006), can I do that?
|
|
|
|
|
SQL Server uses windows clock which then again uses clock from BIOS. So you can have only one time at the server which is the time of all databases.
I don't know what happens if you set up a virtual PC. Since virtual PC has emulated bios it could be possible to have "several times" on one physical server. But... this is just a guess.
|
|
|
|
|
maybe we got into a wrong direction. let me clarify this issue. in one of my databases, I have some tables having timestamp columns, now I just wanna change displayed time for those columns. Can I just use some scripts like: update TABLE ** set **=getdate()
|
|
|
|
|
Sorry, I misunderstood you.
Is the column's data type timestamp? Timestamp-typed columns cannot be modified via DML. Moreover they are not timestamps, but only unique identifiers that database engine update.
|
|
|
|
|
data type for the column is 'datetime', column name could be anything.
|
|
|
|
|
If it's datetime then you can simply set a desired date to the column:
UPDATE TableName SET DateTimeColumn = CONVERT(datetime, '01/01/2006', 101) ...
which will use midnight for time portion
or you can use current date and subtract desired amount of days like:
UPDATE TableName SET DateTimeColumn = GETDATE() - 975 ...
which will today end up to 01/01/2006, but using current time
FYI, if the column name would be timestamp, it is hardcoded to many libraries that is must have timestamp as data type. This will lead to problems in some cases (typically in cursor handling) if the actual datatype isn't timestamp.
|
|
|
|
|
|
|
i created a trigger.. but its not taking effect.. help me plz
CREATE TRIGGER Trig1
ON DB_TEST.tbl_table1
AFTER INSERT, UPDATE
AS
declare @date1 datetime
declare @stat numeric
select @date1=(SELECT datestarted from inserted)
select @stat=(SELECT status_value from inserted)
if (@stat=2 and @date1<GETDATE())
UPDATE tbl_table1 set column1=(datediff(day,GETDATE(), @date1)) from tbl_table1 where status_value=2
else if (@stat=2 and @date1>GETDATE())
UPDATE tbl_table1 set column1='date1 greater than current date' from tbl_table1 where status_value=2
(here the column1 is varchar datatype)
|
|
|
|
|
Why double post?
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
hey... where man its reposted????? just check welll.... if u know the answer kindly help me
|
|
|
|
|
What do you mean it doesn't take effect?
Few points:
- if @stat <> 2 => no update executed
- if @date1 = GETDATE => no update executed
is this the problem?
|
|
|
|
|
The column1 doesnt take any effect.....
is there any errors in my coding?
|
|
|
|
|
Are you meaning that the update isn't updating column1?
Try to add some information to the trigger:
CREATE TRIGGER Trig1
ON DB_TEST.tbl_table1
AFTER INSERT, UPDATE
AS
declare @date1 datetime
declare @stat numeric
select @date1=(SELECT datestarted from inserted)
PRINT 'date1 = ' + convert (varchar(50), @date1)
select @stat=(SELECT status_value from inserted)
PRINT 'stat = ' + convert (varchar(50), @stat)
if (@stat=2 and @date1<GETDATE()) BEGIN
PRINT 'Executing update 1'
UPDATE tbl_table1 set column1=(datediff(day,GETDATE(), @date1)) from tbl_table1 where status_value=2
PRINT 'Rows affected = ' + convert (varchar(50), @@ROWCOUNT)
END
ELSE BEGIN
PRINT 'Condition 1 not met'
END
if (@stat=2 and @date1>GETDATE()) BEGIN
PRINT 'Executing update 2'
UPDATE tbl_table1 set column1='date1 greater than current date' from tbl_table1 where status_value=2
PRINT 'Rows affected = ' + convert (varchar(50), @@ROWCOUNT)
END
ELSE BEGIN
PRINT 'Condition 2 not met'
END
|
|
|
|
|
i m getting Invalid object name 'tbl_table1'. error when i execute the below update command.. help me. i m trying to implement the below in trigger and also in normal update statement
UPDATE tbl_table1 set column1=((datediff(day,column2,column3))+1) from tbl_table1 where column4=1 or column5=7
thanks in advance.....
|
|
|
|
|
Check this[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you then save my time by voting my post.
|
|
|
|
|
thanks man................. i rectified
|
|
|
|
|
Hi All,
I have one strange problem working with my sql server express edition. I have MS SQL Server Express Edition 2005 installed in my computer. When I try to connect to a remote database via Windows authentication I am able to connect to that server successfully. When I try to access the user created database I get an error message –
“The server principal "username" is not able to access the database "dbName" under the current security context”.
But I am successfully able to access all system databases such as master. Also the icon just at the bottom corner of the database server name also does not turn green even after connecting. One more interesting thing is, this problem is happening only to my user id. Rests all in my team are able to access the database using windows authentication with their login id.
I don’t find any problem in working with local database.
Thanks in advance,
Krishnaraj Barvathaya B
|
|
|
|
|
If login to SQL Server via different Windows identified ID than rest of your team, open your login from server and from mapping delete yourself from the database "dbName" , click ok and then add yourself as a user to the database "dbName". Also remember to add relevant roles in the database.
This way your user id should be reset in target database.
|
|
|
|
|
I found answer for this. The reason why I was not able to access the remote data base via Windows authentication was because my user account was not been added as a local admin in the remote computer.
Krishnaraj Barvathaya B
|
|
|
|
|
I have to developed Database comparator tool,i found some source code in .net in this site but not able to find in java.....help required for finding same project in java and oracle is database to compared
|
|
|
|
|
Samarinder wrote: very very urgent ....plz see with high priroty....
No its not, not to me anyway.
Samarinder wrote: I have to developed Database comparator tool,
or did you?
Samarinder wrote: i found some source code in .net in this site
Sounds like you used copy paste
Samarinder wrote: but not able to find in java.....
so you can claim to have written it in java too?
Try google.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi ,
How to use a variable which consists of the sql statement to be used for my cursor . At present the following code gives error
create PROCEDURE spGetAssetRecords
@sSQL varchar(8000)
As
DECLARE c2 CURSOR READ_ONLY
FOR
@sSQL
regards
Vijay
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
