Date as Integer

Database
Enthusiast

Date as Integer

Hello,

I am creating a new column in to an existing Date dimension table

Alter table DB.Test
Add Date_Key INTEGER;

And updating the field with data from  sys_calendar.calendar

And updating that field with the data SQL Below

update DB.Test
set Date_Key = (select (cast(calendar_date as INTEGER)) from sys_calendar.calendar);

But its throwing some error 3669:More than one value was returned by a aubquery, any ideas why its doing?

25 REPLIES
Teradata Employee

Re: Date as Integer

What are the other columns in your table Test?

You need to have a join in an update based on same [but uniquely identifying] columns.

Junior Contributor

Re: Date as Integer

You're trying to update every row in Test with all 73414 in sys_calendar. 

There should be a DATE column in a calendar table:

update DB.Test
set Date_Key = cast(calendar_date as int);
Enthusiast

Re: Date as Integer

There is already a calendar date column in the table, i am trying add the same date column  as Int, which will be used as unique id and easy to join as it will be integer.

Enthusiast

Re: Date as Integer

Just a question here, Why do you presume that the integer will be 'easy to join'?

As Dieter and Adeel have pointed out, your second update statement is in error.  You're telling the system to update every row with every date from the sys_calendar.calendar view.  If you've already got a calendar date column on the table (and i understand what you're trying to explain)...your update statement should look exactly like what Dieter posted.

You want to update the new column with the calendar date in the same row cast as an integer right?

Dieter's sql will do that as long as you make sure the column names are correct, since we don't have the table ddl, a template is about as good as it gets.

Enthusiast

Re: Date as Integer

I am trying to achieve if the date is 28/11/2004 i need to and store as 28112004, but using the above sql kindly provided by Dieter  it is coming as 1041128. one more example, for 26/04/2010 it is coming as 1100426 instead od 26042010. Not sure what it is doing?

Junior Contributor

Re: Date as Integer

CAST(datecol AS INT) returns TD's internal DATE format. You want something different:

extract(year from datecol) * 10000 + 
extract(month from datecol) * 100 +
extract(day from daycol)

or

cast(datecol as int) + 19000000

But IMHO a join of a DATE to a DATE is not more/less complicated that a join of an INT to an INT. 

You just loose all date functionality when you switch to INT :-)

Teradata Employee

Re: Date as Integer

It would be better to store it as a date, but when you need to show it on a frontend, you can use CAST with FORMAT to specify the required format i.e. 'DDMMYYYY'.

HTH!

Enthusiast

Re: Date as Integer

Thanks Adeel,

Can i please ask you for one more favor, i have gone through many articles and forums but couldn't manage to figure out the best method to impelement SCD type 2 objects in a table. In my data cube i have altogether 4 tables out of which 2 tables have got few columns of SCD type 2 (One table with 2 and other with 3 columns). Can you please suggest me the best method to deal with these obejcts in TD12, also please provide me the basic code to implement it. Please note that i am new to Databases and especially to data modelling and Tera Data. So please provide me a very descriptive code and how/where to implement it.

I have created the tables in TD SQL Assistance based on an existing OLTP table.

Many Thanks for your favour.

Teradata Employee

Re: Date as Integer

A simple way to cater for SCD Type 2 would be as follows:

Assume you have a table as below:

Table1

ID INT,

NAME VARCHAR(10),

ADDRESS VARCHAR(50)

And you wish to track history on the basis of 'Address can change for an ID/Name'.

Hence you add two more columns as below:

START_DATE DATE,

END_DATE DATE

And now whenever you insert new data, you set:

START_DATE as CURRENT_DATE

END_DATE as NULL [or HIGH DATE i.e. 9999-12-31]