Few basic queries

Database
Enthusiast

Re: Few basic queries

Thanks for your reply.

If I want to take the month name as "MON" i.e. MAY - from a timestamp datatype, then how do I have to cast it.
Similar questions if I want to take the week no/ quarter no/ year from a timestamp field then how do I have to cast it.
Highlighted
Ambassador

Re: Few basic queries

There's the Standard SQL EXTRACT function to retrieve parts of a timestamp like YEAR/MONTH/DAY/HOUR/MINUTE/SECOND as an integer. That function also exists in Oracle.

WEEK and QUARTER is not implemented, of course QUARTER is easy to calculate, but WEEK is horrible in plain SQL.
In a typical environment it's recommended to join to a calendar-table instead of doing the same calculation over and over again, as day/month/year/week/quarter of a given date never changes.

Extracting dates as strings (similar to Oracle's TO_CHAR) is done by a FORMAT followed by a CAST:

SELECT CAST((CURRENT_TIMESTAMP (FORMAT 'mmmByyyy,BddBhh:mi:ss')) AS CHAR(30))

Dieter
Enthusiast

Re: Few basic queries

Hi,

I am looking for a front end application where in backend the database will be in Teradata. The front end application will be used to create/modify users for a new application etc. Actually I am looking for something like HTMLDB which is available with Oracle.

Can you please let me know that what we can use in this case.
Enthusiast

Re: Few basic queries

Hi,

Can you please let me know what is equivalent statement for DBMS_OUTPUT.PUT_LINE in Teradata.
Enthusiast

Re: Few basic queries

Hi,

Can you please also let me know that is there any concept of static and dynamic dictionary tabes in Teradata.
Enthusiast

Re: Few basic queries


You can't directly extract week no or quarter no directly form a timestamp but you can use a join on CALENDAR_DATE in CALENDAR table and get WEEK_OF_{MONTH|YEAR|CALENDAR} and QUARTER_OF_{YEAR|CALENDAR} corresponding column values
Enthusiast

Re: Few basic queries

Hi,

I think we can find out week no, month of the year etc from the view sys_calendar.calendar, like below:

select * from sys_calendar.calendar where year_of_calendar=2008 order by calendar_date

Please correct me if I am wrong.

Can you please also help me in my following queries :

1. I am looking for a front end application where in backend the database will be in Teradata. The front end application will be used to create/modify users for a new application etc. Actually I am looking for something like HTMLDB which is available with Oracle. Can you please let me know that what we can use in this case.

2. Can you please let me know what is equivalent statement for DBMS_OUTPUT.PUT_LINE in Teradata.

3. Can you please also let me know that is there any concept of static and dynamic dictionary tabes in Teradata.
Enthusiast

Re: Few basic queries

Hi,

In addition to the above queries can you please also let me know what is the basic difference between a macro and a procedure in teradata. What ever we are doing in a macro that we can do in a procedure as well then what is the basic requirement of having a macro.