Current timestamp in particular format

Database
N/A

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 :

SELECT * FROM TABLE WHERE CHG_TS > #XYZ# and CHG_TS < (SELECT CURRENT_TIMESTAMP)

Note : #XYZ# denotes the last processed timestamp.

 

Thanks in advance.

3 REPLIES
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

Ansul,

 

 

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.