Thanks Jay. Me too... SQL Server / Oracle.
But my favorite predilection is attempting vi commands in notepad and vice versa.
-----Original Message-----
From: Taylor Jay
[mailto:oracledba-ezmlmshield-x92068160.[Email address protected]
Sent: Monday, January 29, 2007 1:40 PM
To: LazyDBA Discussion
Subject: RE: CURRENT_TIMESTAMP vs GETDATE
Correct it should sysdate, sorry in and out of different db's...
Calvin/Chris,
Those are good points...thanks.
The different values just struck me as odd. I was hoping our test were
not invalidated.
Cheers-
Jay
-----Original Message-----
From: Chris
[mailto:oracledba-ezmlmshield-x33785986.[Email address protected]
Sent: Monday, January 29, 2007 16:26
To: LazyDBA Discussion
Subject: RE: CURRENT_TIMESTAMP vs GETDATE
Could the Local Session Time Zone be the cause?
BTW-Is Getdate() new to 10g. I don't see it in the 9i docs. But it is a
known SQL Server function. Or do you mean sysdate?
CURRENT_TIMESTAMP returns the current date and time in the session time
zone, in a value of datatype TIMESTAMP WITH TIME ZONE. The time zone
displacement reflects the current local time of the SQL session. If you
omit precision, then the default is 6. The difference between this
function and LOCALTIMESTAMP is that CURRENT_TIMESTAMP returns a
TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP
value.
In the optional argument, precision specifies the fractional second
precision of the time value returned.
Examples
The following example illustrates that CURRENT_TIMESTAMP is sensitive to
the session time zone:
ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ---------------------------------------------------
-05:00 04-APR-00 01.17.56.917550 PM -05:00
ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ----------------------------------------------------
-08:00 04-APR-00 10.18.21.366065 AM -08:00
If you use the CURRENT_TIMESTAMP with a format mask, take care that the
format mask matches the value returned by the function. For example,
consider the following table:
CREATE TABLE current_test (col1 TIMESTAMP WITH TIME ZONE);
The following statement fails because the mask does not include the TIME
ZONE portion of the type returned by the function:
INSERT INTO current_test VALUES
(TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
The following statement uses the correct format mask to match the return
type of CURRENT_TIMESTAMP:
INSERT INTO current_test VALUES (TO_TIMESTAMP_TZ
(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM'));
-----Original Message-----
From: Calvin Shafer
[mailto:oracledba-ezmlmshield-x75083139.[Email address protected]
Sent: Monday, January 29, 2007 1:11 PM
To: LazyDBA Discussion
Subject: RE: CURRENT_TIMESTAMP vs GETDATE
Correction: Maybe the timestamp is not affected by DST changes *so* they
are
accurate when determining the length of time between log entries?
-----Original Message-----
From: Calvin Shafer [mailto:[Email address protected]
Sent: Monday, January 29, 2007 4:10 PM
To: 'LazyDBA Discussion'
Subject: RE: CURRENT_TIMESTAMP vs GETDATE
Maybe the timestamp is not affected by DST changes to they are accurate
when
determining the length of time between log entries?
-----Original Message-----
From: Taylor Jay
[mailto:oracledba-ezmlmshield-x40123612.[Email address protected]
Sent: Monday, January 29, 2007 3:18 PM
To: LazyDBA Discussion
Subject: RE: CURRENT_TIMESTAMP vs GETDATE
Chris,
I agree that they should have returned the same value. We updated the
Unix
box with a patch for the new date for DST. We then did a test via a qry
tool
and our applications to make sure everything was working. It was. We
then
moved the date on the server to 11-Mar-07 at 01:30. At 02:00 the server
properly jumped to 03:00, however at this point the CURRENT_TIMESTAMP
and
the GETDATE returned different values. Here in
lies my interest. As stated below "Returns the current date and time.
This function is equivalent to GETDATE.", what makes them equivalent and
not
identical? The GETDATE reflected the server time change the
CURRENT_TIMESTAMP did not. Any ideas?
Thanks again.
Cheers-
Jay
-----Original Message-----
From: Chris
[mailto:oracledba-ezmlmshield-x47222144.[Email address protected]
Sent: Monday, January 29, 2007 14:28
To: LazyDBA Discussion
Subject: RE: CURRENT_TIMESTAMP vs GETDATE
From BOL (2005).
"Returns the current date and time. This function is equivalent to
GETDATE."
-----Original Message-----
From: Taylor Jay
[mailto:oracledba-ezmlmshield-x31449968.[Email address protected]
Sent: Monday, January 29, 2007 9:35 AM
To: LazyDBA Discussion
Subject: CURRENT_TIMESTAMP vs GETDATE
Can anyone provide some insight into why these two commands would return
different values. We did some testing of the patches on the new day
light
savings time schedule this weekend. During the testing it was brought to
light that these two commands were returning different values.
Formatting
aside, they were different. Should these not return that same data and
time?
Thanks in advance for any explanation.
Cheers-
Jay
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba
job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html
--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.
Douglas County, Oregon
www.co.douglas.or.us
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba
job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba
job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.
Douglas County, Oregon
www.co.douglas.or.us
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
--
This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
Douglas County, Oregon
www.co.douglas.or.us
Oracle LazyDBA home page