|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/14/2007 12:25:00 AM
Posts: 6,
Visits: 1
|
|
I am a new user of Teradata sql and etl process. I need some information regarding creating tables and datatypes. Here is my issue. I have data in sql server that I am trying to bring into teradata. The table is pretty straight forward in sqlserver and looks like this,
CREATE TABLE SensorData (
RecordId int IDENTITY(1000,1),
ServerId int NULL,
SensorId int NULL,
ServerDate datetime NULL,
CreateDate datetime NULL DEFAULT CURRENT_TIMESTAMP,
ValueA money NULL,
ValueB money NULL,
State varchar(15) NULL,
Humidity money NULL,
Temperature money NULL,
Analog money NULL,
SensorType tinyint NULL,
InHrOfOp bit NOT NULL DEFAULT 0
)
in Teradata I am trying to use the following,
CREATE TABLE edwdev.Counter_SensorData, NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
RecordId INTEGER NOT NULL,
ServerId INTEGER,
SensorId INTEGER,
ServerDate DATE FORMAT 'YYYY-MM-DDBHH:MI:SSBT',
CreateDate DATE FORMAT 'YYYY-MM-DDBHH:MI:SSBT' DEFAULT CURRENT_TIMESTAMP,
ValueA varchar(5) CHARACTER SET LATIN NOT CASESPECIFIC,
ValueB varchar(5) CHARACTER SET LATIN NOT CASESPECIFIC,
State varchar(15) CHARACTER SET LATIN NOT CASESPECIFIC,
Humidity varchar(4) CHARACTER SET LATIN NOT CASESPECIFIC,
Temperature varchar(4) CHARACTER SET LATIN NOT CASESPECIFIC,
Analog varchar(4) CHARACTER SET LATIN NOT CASESPECIFIC,
SensorType char(1) CHARACTER SET LATIN NOT CASESPECIFIC,
InHrOfOp char(1) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX XPKCOUNTER_SENSORDATA (RecordId);
When I run the above I get an error,
Invalid FORMAT string 'YYYY-MM-DDBHH:MI:SSBT' (3530)
The data in the source system is in the format,
11/29/2006 11:50:00 PM
and that is how I plan on bringing the data into the target (teradata).
Does anyone see why I am getting the above error? I looked in SQL Reference and it seems that I can use the above date format.
I would appreciate any help here and if I solve this, I will post my findings on here as well.
Thank you,
Kuldeep
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/14/2007 12:25:00 AM
Posts: 6,
Visits: 1
|
|
After I posted this, I changed the query to,
CREATE TABLE edwdev.Counter_SensorData, NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
RecordId INTEGER NOT NULL,
ServerId INTEGER,
SensorId INTEGER,
ServerDate TIMESTAMP FORMAT 'YYYY-MM-DDBHH:MI:SSBT',
CreateDate TIMESTAMP FORMAT 'YYYY-MM-DDBHH:MI:SSBT' DEFAULT CURRENT_TIMESTAMP,
ValueA varchar(5) CHARACTER SET LATIN NOT CASESPECIFIC,
ValueB varchar(5) CHARACTER SET LATIN NOT CASESPECIFIC,
State varchar(15) CHARACTER SET LATIN NOT CASESPECIFIC,
Humidity varchar(4) CHARACTER SET LATIN NOT CASESPECIFIC,
Temperature varchar(4) CHARACTER SET LATIN NOT CASESPECIFIC,
Analog varchar(4) CHARACTER SET LATIN NOT CASESPECIFIC,
SensorType char(1) CHARACTER SET LATIN NOT CASESPECIFIC,
InHrOfOp char(1) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX XPKCOUNTER_SENSORDATA (RecordId);
and it works now. I can create the table. I changed 'DATE FORMAT' to 'TIMESTAMP FORMAT' and it works. Is that what I was doing wrong?
Thanks,
Kuldeep
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/16/2009 3:03:20 PM
Posts: 152,
Visits: 454
|
|
Teradata has DATE, TIME, TIME WITH ZONE, TIMESTAMP and TIMESTAMP WITH ZONE data types.
SQL Server DATETIME maps to Teradata TIMESTAMP data type.
I suggest downloading
SQL Reference: Data Types and Literals.
--Cal
|
|
|
|