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.
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.
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.