Getdate() Problem: Where Is The Time Taken From ?
Apr 25, 2007
Hi,
I have a funny situation.
Within: MSSQL 2000 SP3, everything below described is running on same
PC.
there is a program running, which sends information to two other
programs.
This information is a timestamp of the program in datetime format,
which has it's own clock.
The clock is incremented each 5 seconds of the program, which
corespondes to aprox. one second of the real time.
It means, each on second of real time, the computer time is updated +5
seconds.
Now, two other applications, are getting this information at the same
moment.
FIRST of this applications, updates local time of the computer with
the time recieved.
SECOND application, writes a protocol to file, with timestamp read at
moment of writing from operating system.
Until now, all times are equal (the differences are not biger that
ms).
Now, the SECOND application, after writing a log into file (with
proper timestamp), calls SP in database.
It passes as input prm. the time recieved from very first program,
which is the same time as the current system time, which is the same
time the SECOND application writes to the log file.
This SP (besides other things) at the very beginning writes a log into
table, where two times are logged:
- getdate() to first column,
- timestamp recieved as input parameter.
Now the funny thing.
I would expect, the times are equal.
getdate() = '2007.04.25 10:00:00.000'
prm_recieved = '2007.04.25 10:00:00.000'
I would expect, that the time from getdate() will be shifted with
miliseconds (because of call etc).
getdate() = '2007.04.25 10:00:00.123'
prm_recieved = '2007.04.25 10:00:00.000'
I would even expect, that the time is shifted 5 seconds ahead:
getdate() = '2007.04.25 10:00:05.000'
prm_recieved = '2007.04.25 10:00:00.000'
or, 5 seconds and some miliseconds:
getdate() = '2007.04.25 10:00:05.123'
prm_recieved = '2007.04.25 10:00:00.000'
What I can not UNDERSTAND, why sometimes the time is equal, or
sometimes is ALMOST equal (within the diff of miliseconds), and why
sometimes the time is like this(!!!) :
getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'
It seams to me, the getdate is getting somehow the PERVIOUS local
system time, which was acctualy already upgraded ! Becasue all other
app's are having the proper value.
All other apps are writen in C++ and are very simple.
I was trying to set the SQLServer running property higher - with no
result.
I need to mention, there is SQLServer Agent running, and one procedure
with endless loop, with waitfor delay equal 2 seconds.
But non of them (changing the waitfor delay to other value, disabling
SQLAgent) fixes the problem.
Can somebody then tell me, where from is the time taken, or what is
the root problem of this issue?
Or what can it be?
Best regards,
Matik
View 6 Replies
ADVERTISEMENT
Nov 12, 2007
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 8 Replies
View Related
May 26, 2006
Is there a command that will let me set getdate() a in a smalldatetime field so that the there is no time associated with it?
For example, I have a table that I want to load the date a user does an action. If I use getdate() I'll get a value such as 5/25/2006 08:26:56.340, whereas I would just like a value 5/25/2006.
I can work it out by doing the following: select (datename(month,getdate())+'-'+datename(day,getdate())+'-'
+datename(year,getdate()))
However it seems to me that there should be a simpler way.
View 4 Replies
View Related
Nov 7, 2000
I've been using the GetDate() function to populate a column in a table.
But it populates it with both the date and time: 2000-11-08 11:22:28
I'd like it to just put in the date: 2000-11-08
I've tried the Convert function as well as other functions, to no avail.
Some attempts have reversed month and day (I'm in Australia, so all PCs and Servers are set with a dd-mm-yy date format)
Any suggestions please?
View 1 Replies
View Related
Jul 4, 2007
Hi,I am creating creating a table with a Date column dd-mm-yyyy. But Icant seem to find a SQL function that just returns today's date.getDate() returns the time as well so I cant use it.The reason is simply that I want to update/overwrite over and overagain all records from current day but not touch the ones fromyesterday etc and with the timestamp in there I just end up addingmore and more rows for the same day.In other words I only want to preserve rows are from yesterday orolder but overwrite ones from today.Any help will be appricated.Thank you!Yas
View 3 Replies
View Related
Sep 1, 2006
the ssis expression language getdate() function returns the current date with the current time. i only need to get the current date, without the current time. for example: 9/1/2006
how would i construct the proper expression to return this value?
thanks in advance.
View 2 Replies
View Related
Mar 14, 2007
I have used the GETDATE() function within an expression to create a directory name based on the current date. I am in the Sydney time zone and the new day's folder name doesn't change until after 11 am - so GETDATE() is picking up the date and not adjusting for the time zone. How do I either set the time zone within the package or make the GETDATE() function look at time zone of the system on which it is run?
View 3 Replies
View Related
Jun 23, 2015
What is the proper way to ensure when pulling date between two getdates, that you include from midnight of the first getdate to 23:59 hours in the second getdate?
WD.WRKD_WORK_DATE
and WD.WRKD_WORK_DATE between DATEADD(DD, - 11, GETDATE())
and DATEADD(DD, - 5, GETDATE())
View 3 Replies
View Related
Oct 4, 2000
Please i need an exmple of ur solution, thanks :)
I'm using some files to show certain pages on certain date for an example
File name : aa.doc
start date: 10/02/00
end date : 10/03/00
But it expires on 10/02/00, here is the strored procedure:
Before the date comes, it expires the page
Here is my stored procedure:
"
SELECT startdate, enddate,archivedate
and (startdate is null or (getdate() >= startdate and getdate() <= enddate))
and (archivedate is null or (getdate() <= archivedate))
group by startdate, enddate order by startdate desc "
Thankx a lot
View 1 Replies
View Related
Sep 26, 2007
select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)
From above query I get
mmddyyyyhhmm
but it' s yyyy and hour can not be separated
04/12/200702:05
How can I separated the year and hour ?
Thanks
Daniel
View 2 Replies
View Related
Aug 7, 2007
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 5 Replies
View Related
May 13, 2008
When I use the getDate() function I get something like
2005-03-09 15:13:45.017
but I don't want the time, just 2005-03-09
how do you strip out the time from the result?
Thanks
View 2 Replies
View Related
May 28, 2008
How can i getdate in C# code, like we do in SQL Server ?
Thanks.
View 1 Replies
View Related
Apr 27, 2001
However, when I run select getdate() in Sql 6.5 I get the following
Apr 27 2001 11:59AM
Both queries are run from the QueryIsql window.
How can I get the sql 7 version of getdate to be exactly the same as the
sql 6.5 version from the query window without resorting to usign the
convert function?
According to Sql 7 books online, getdate
"Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values"
View 1 Replies
View Related
Oct 3, 2000
I'm facing some strange problem here:
-----------------------------------------
I'm using some files to show certain pages on certain date for an example
File name : aa.doc
start date: 10/02/00
end date : 10/03/00
But it expires on 10/02/00, here is the strored procedure:
Before the date comes, it expires the page
Here is my stored procedure:
"
SELECT startdate, enddate,archivedate
and (startdate is null or (getdate() >= startdate and getdate() <= enddate))
and (archivedate is null or (getdate() <= archivedate))
group by startdate, enddate order by startdate desc "
Thankx a lot
View 2 Replies
View Related
Sep 20, 2000
i am also trying to write a query that
retrieve information on yesterdays new registered
member. So far as i am concern with sqlserver you use
the getdate()-1 to retrieve this information. But the
problem i get when i try to use the getdate()-1 is
that i tend to get todays and yesterdays registered
users. Is there anywhere that i would be able to
retrieve information on just yesterdays new registered
members and also only todays new register members.
View 1 Replies
View Related
Sep 28, 2000
The following WHERE clause does not generate a datetime from my GETDATEs. Can anyone help me out on the syntax?
WHERE (FROMTIME = GETDATE() AND ToTime = GETDATE())
Thanks much.
TM
View 7 Replies
View Related
Aug 7, 2003
Is it right, that getdate() cannot be used in an user defined function. if so, then how do i get hte current date and time in my function?
View 2 Replies
View Related
May 19, 2005
Hi
I know this is going to sound really daft, but what is the easiest way of getting today's date without the time? Getdate() on it's own doesn't work because it always brings out the time. I know I'm missing something really basic but I can't figure it out.
Please help.
Thanks
View 3 Replies
View Related
Aug 17, 2005
How to get the first and last day of the previous month using function getDate()?
View 2 Replies
View Related
Oct 20, 2004
G'day,
I want to display the current time minus 5 minutes. say
2004-03-29 11:43:49.123 to be 2004-03-29 11:38:49.123.
Is there any Equivalent to the DB2's CURRENT TIMESTAMP - 5 MINUTES which will display the current day say 2004-03-29 11:43:49.123 as
2004-03-29 11:38:49.123
cheers
Melb
View 2 Replies
View Related
May 19, 2006
I'm using getdate() - 74 and getting something like
2006-03-06 11:26:02.870
Is there a simple way to get
2006-03-06 00:00:00.000
instead?
Or just get the date and not the time part. I'm using this date with a between function and the time component is throwing it off.
I'm using
cast(convert(char, getdate(),112) as datetime) - 74
and it works...but there must be an easier way...
View 6 Replies
View Related
Feb 10, 2007
Hi,
when I used getdate() fuction, its giving the correct time in this format 10/02/2007 01:04:31 PM. But I need the time in 24 hour format. Means it should be like 10/02/2007 13:04:31.
How to get it. Please help me!!
shaji
View 5 Replies
View Related
May 21, 2007
select
H.IsoDate,
HI.Country,
HI.Center,
H.ExchangeCode,
HC.Holiday,
H.HolidayCode
from
Holidays H
join
HolidayInfo HI
on
HI.Code = H.ExchangeCode
join
HolidayCodes HC
on
H.HolidayCode = HC.Code
where
H.isodate = getdate()
This is a script but the getdate part is not working can somebody point me in the right direction where i might be going wrong.
The date format in H.isodate is like this:
2005-06-24 00:00:00.000
cheers in advance
View 2 Replies
View Related
Dec 27, 2007
Hello,
I'm new to SQL server and I'm trying to insert the system time into a table.
I'm using following insert statement. But I get an error 'incorrect syntax near the keyword select'
INSERT INTO today(datum) VALUES(SELECT getdate() FROM today)
The datatype of datum is Datetime
Can someone please tell me what I'm doing wrong
Thanks
View 1 Replies
View Related
Mar 13, 2008
I am setting up a table and I want to capture the creation date of each record when it comes in. Obviously the easy way to do this is set the dfault value of this field to getdate(), but I am wondering if there is a benefit to using an insert trigger instead?
I will also be adding a seperate updatedOn field and plan to use an update trigger to populate it. If there is a better way to do either of these please let me know.
View 3 Replies
View Related
Mar 13, 2008
Folks:
I use,
PRINT CONVERT(VARCHAR, GETDATE())
To write the current date to the console (for debugging purposes). Only problem is it only shows hours and minutes (not seconds). Can anyone suggest an equivalent that shows seconds as well?
View 1 Replies
View Related
Mar 27, 2007
Hi if i in a table using getdate then
the field get the date from the Machine this is all right
But when i use getdate in my Query then i get Zerro records Why?
Alvin
View 6 Replies
View Related
Jan 5, 2007
Hi ,
I have problem with Getdate()
i want to create Guest book with VWD 2005 and SQL 2005
I have same problem here for Q1,
http://forums.asp.net/thread/1513111.aspx
I put the Defualt value for the date : getdate()
and then I create insert page, but i got the error below
I tried to [Allow Null] for the data field in sql 2005, but when I add new entries, there is no date entered to the DB.
Can you Help.
thank you
---------------------------------------
Cannot insert the value NULL into column 'guestdate', table 'tour2.dbo.gues'; column does not allow nulls. INSERT fails.The statement has been terminated
View 2 Replies
View Related
Aug 15, 2007
Hello All,
I have a problem as follows
if i execute SELECT GETDATE() statement multiple times in a single run it returns me the same datetime without any difference in even milliseconds.
I am unable to figure out what is wrong. I am assuming that whenever executed in a transaction it will give the same result.
could anybody let me know what is correct. Thanks for your help in advance.
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
SELECT GETDATE()
even then i get the same date.
View 6 Replies
View Related
Feb 27, 2008
Hi,I want to fetch all records of a table where the datefield (datetime field) equals getdate().select * where mydatfield = getdate()but this gives nothing because getdate() which also contains the min. and seconds, is always greater than the data field which contains 00:00 as min/seconds.Any help would be appreciated.ThanksTartuffe
View 8 Replies
View Related
Apr 11, 2008
hi allcan somebody tell me how can i insert todays date in sqlserver 2000 in dd/mm/yyyy format...i knw we use getdate but how exactly..plz write the code for me...i have datetime as datatype for my column name.....i want it in this format because i am using a datetimepicker to compare values while retreiving the data..and this datetime picker is taking dd/mm/yyyy format so i want to insert records in that format...plz helpthanks in advance
View 12 Replies
View Related
Oct 29, 2005
Can anybody tell me, how can i avoid the seconds in getdate().I want only date and time (hh:mm) format.Thanks in advance
View 2 Replies
View Related