Current timestamp in particular format

New Member

Current timestamp in particular format

Hello All,


I know this question is asked multiple times but would like to ask you again. Since I m bit confused with teradata. And confused with timestamp between Oracle and teradata :(


I have a table which will have the change_indicator and change_timestamp(timestamp(6)) to tell me delta data. Now I want to implement the ETL delta process.So I want the pull only that data which is not processed for that I need to pass the parameter to the query to pull the data based on timestamp.

Now the problem is table timestamp data looks like '11/4/2016 15:32:49.990000', but will passing the timestamp I need to pass like '2016-11-04 15:32:49.000000' and if i pull the current_timestamp its again displayed in table format (ie '11/4/2016 15:32:49.990000'). So is there a way I can make all the timestamp in same format.


And also,is there any way where we can change the timestamp format for the DB. so that i can just pull the current timestamp and pass it to the query.

For ex :


Note : #XYZ# denotes the last processed timestamp.


Thanks in advance.

Teradata Employee

Re: Current timestamp in particular format

select current_timestamp (format'Y4-MM-DDBHH:MI:SSDS(6)')


This is close to the default installation format, so someone changed it on your system using the tdlocaledef utility.  The only way I have found to get it in the format you describe is to use the above format phrase on each select statement.

Teradata Employee

Re: Current timestamp in particular format

Also - you could modify the table so that the pertinent timestamp columns have this format.


Re: Current timestamp in particular format




GJColeman has provided the short answer.


Actually Oracle and Teradata act the same on the date.


I think here you need to understand two things:

1. A data column in any DB is a "date" column. You don't need any date conversion when comparing date columns in DB. Even the date is present in a different format in your query tool.

2. "I need to pass like '2016-11-04 15:32:49.000000" this can work because Teradata provides the conversion for you automatically.