All Forums Enterprise
marcmc 112 posts Joined 12/05
28 Sep 2007
TIMESTAMP

I have a stored procedure that sets a variable as follows:SELECT CAST(CURRENT_TIME(2) AS TIMESTAMP(2)) However, it has returned '2007-09-26 09:12:12.01' when it should have returned '2007-09-27 09:12:12.01'.The only element of the date being incorrect is the day of month.Is this something we know about in Teradata?

famalau 43 posts Joined 08/07
01 Oct 2007

A couple of question that might help:1) The CURRENT_TIME value is obtained on the Teradata server. Is the clock of that server(s) properly set?2) Are you working with Teradata for Unix or Windows? Are there some different time zone than yours set at Operational System level?Good luck!

marcmc 112 posts Joined 12/05
02 Oct 2007

1.) Depends on your definition of properly set. Timezone is set as GMT - Dublin, Edinburgh etc with automatic adjustments for Daylight Savings. We are in Ireland. The Actual time is synched with timeserver time.windows.com2.) Underlying OS on our Teradata Servers is Windows Server 2003. My PC would be synched with Domain at logon, not sure what master time server is used for this.Remembering that the consistent issue is with the day of month element only have we anymore ideas?

NetFx 297 posts Joined 09/06
02 Oct 2007

Did you try CURRENT_TIMESTAMP as oppose to CURRENT_TIME?Are CURRENT_TIMESTAMP and CURRENT_DATE also off by 1?

marcmc 112 posts Joined 12/05
03 Oct 2007

When I run the below statements in Queryman the 1st gives me a wrong date and the 2nd one is correct. When my colleagues do the same they get 2 correct dates.When I run them in BTEQ they are both correct.I'm not sure if this causes the underlying issue as it clouds the issue further.I am going to reboot & have coffee just to be sure.SELECT CAST(CURRENT_TIME(2) AS TIMESTAMP(2)); -- 2007-09-29 15:47:36.94SELECT CAST(CURRENT_TIMESTAMP(2) AS TIMESTAMP(2)); -- 2007-10-03 15:47:37.06

sudhiroracle 8 posts Joined 10/07
03 Oct 2007

Hi Supreme Being,What is the difference between the CURRENT_TIME and CURRENT_TIMESTAMP ? is it only in the output format ? RegardsTenacity88.

sudhiroracle 8 posts Joined 10/07
03 Oct 2007

Hi Marcmc,My appologies, i mistook the title " Supreme Being " as your name in my previous post. Hope you would pardon me :-).regardsTenacity88.

NetFx 297 posts Joined 09/06
03 Oct 2007

Conceptually there are two differnt algorithms for converting "TIME WITH ZONE" (CURRENT_TIME) to "TIMESTAMP". a) Convert "TIME WITH ZONE" to "TIME" and then add current the System Date to it to create "TIMESTAMP". b) Convert "TIME WITH ZONE" to "TIMESTAMP WITH ZONE" and then convert to "TIMESTAMP".I think Teradata uses a.This is the result on my system using BTEQSET TIME ZONE INTERVAL'11:00' Hour To Minute; *** Set SESSION accepted. *** Total elapsed time was 1 second.select cast(TIME'23:00:00+00:00' as TIMESTAMP); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. 23:00:00+00:00--------------------------2007-10-03 10:00:00.000000select cast(TIMESTAMP'2007-10-03 23:00:00+00:00' as TIMESTAMP); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. 2007-10-03 23:00:00+00:00--------------------------2007-10-04 10:00:00.000000Note that the dates are off by 1.I suggest CURRENT_TIMESTAMP.

Jim Chapman 449 posts Joined 09/04
03 Oct 2007

There is a related known problem, DR 114070: "CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP do not return the correct value for the default session time zone. Instead, they returned the year, month, day, hour, and minute having the same value no matter what the session time zone is. Therefore, CURRENT_TIME and CURRENT_TIMESTAMP are only correct when the session time zone is equal to the time zone specified in DBSControl."If this problem impacts your application, contact the Teradata Support Center for fix availability info.

marcmc 112 posts Joined 12/05
04 Oct 2007

thanks all, i appreciate all your efforts.

marcmc 112 posts Joined 12/05
25 Oct 2007

FYITeradata have this raised under DR 117392.This will be shipped with v2r12.

marcmc 112 posts Joined 12/05
25 Oct 2007

also that the workaround is as I suggested which is to use(CAST(CURRENT_TIMESTAMP(2) AS TIMESTAMP(2))over (CAST(CURRENT_TIME(2) AS TIMESTAMP(2))Hope this helps.

monty4u2 1 post Joined 04/10
29 Apr 2010

I am trying to convert a varchar into timestamp and I am not able to. Can any one please help.
SELECT
CAST (TRIM(SUBSTRING (T.CREATED_DATE_TS FROM 0 FOR 23)) AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS') AS CREATED_DATE_TS

FROM TABLE T

Throws an Invalid Timestamp Error. I got it from Oracle and dumped into a csv and then load it into a staging table using varchar. Now I am trying to write a view on top of this staging table and load it into a teradata table.

The data is like this
4/13/2010 12:51:48 PM
4/13/2010 2:56:55 PM
4/13/2010 5:45:10 PM
4/13/2010 1:21:16 PM

simhadrijk 18 posts Joined 04/10
03 May 2010

Hi monty,

The approached followed by you is not proper.
You can cast improper date values to timestamp in teradata;
Try running the following query
SELECT

CASE WHEN TRIM( SUBSTR(T.CREATED_DATE_TS,0,INDEX(T.CREATED_DATE_TS,'/')) ) > 9
THEN TRIM( SUBSTR(T.CREATED_DATE_TS,0,INDEX(T.CREATED_DATE_TS,'/')) )
ELSE TRIM('0') || TRIM( SUBSTR(T.CREATED_DATE_TS,0,INDEX(T.CREATED_DATE_TS,'/')))
END AS day1

,CASE WHEN TRIM( SUBSTR(T.CREATED_DATE_TS,INDEX(T.CREATED_DATE_TS,'/') + 1,INDEX(T.CREATED_DATE_TS,'/')) ) > 9
THEN TRIM( SUBSTR(T.CREATED_DATE_TS,INDEX(T.CREATED_DATE_TS,'/') + 1,INDEX(T.CREATED_DATE_TS,'/')) )
ELSE TRIM('0') || TRIM( SUBSTR(T.CREATED_DATE_TS,INDEX(T.CREATED_DATE_TS,'/') + 1,INDEX(T.CREATED_DATE_TS,'/')))
END AS mnth1

,TRIM( SUBSTR(T.CREATED_DATE_TS, (INDEX( (SUBSTR(T.CREATED_DATE_TS , INDEX(T.CREATED_DATE_TS,'/')+1 )),'/') + INDEX(T.CREATED_DATE_TS,'/') ) + 1 , 4)) AS yr1

FROM table t

so that you will get a fair idea what i am speaking.

Rather than casting from staging, you can have them while loading from CSV to staging.
something like
Nullif(:field_DTE_TIME, '9999-12-31 23:59:59') (TIMESTAMP(6), FORMAT 'YYYY-MM-DD HH:MI:SS.SSSSSS)

even though you store it as varchar, it will be easily casted to timestamp later point of time.

Hope this helps you.

Have fun.

Jagdish Kumar Simhadri

simhadrijk 18 posts Joined 04/10
03 May 2010

yup sorry i meant

"You can not cast improper date values to timestamp in teradata;" in previous phrase

Jagdish Kumar Simhadri

You must sign in to leave a comment.