Hello,
 
I am running a BTQ script which is generating a .CSV file.
One of the column in the file is a number but datatype for the same in the parent table is defined as CHAR.
I am using a cast function to convert it into bigint( number is 13 digits long), results are fine when I am running this query in SQL Assiatant.

My customer has loaded a TD test table with a date & time column that looks like this:
Column:      XXXXXX   varchar(23) 
Data:
7/31/2014 9\:20\:03 AM
8/4/2014 8\:23\:15 AM
12/6/2014 2\:31\:07 PM 
 
What is the quickest/easiest way to convert the data to TIMESTAMP(6),  if possible at all ?   

Hi,
I am a newbie in teradata and so am not familiar with casting/ formatting concepts...
can you please clarify the below queries...
1. I have an export using below syntax

Hello
I keep getting the above error message after the query below has been running for a minute or so.

I looked at this part...

left join (select CCT_External_ID, CCT_Agent_ID from GDW_VIEWS. CCT_AGENT where CCT_External_ID between '0' and '999999') Rep_Trans_IDs on Rep_Emp_IDs.emp_id=Rep_Trans_IDs.CCT_External_ID

Hi everyone,
I'm tryng to build a set of queries with the objective of getting the DDL of tables only from the info inside the dbc tables (columns, tvm, dbase, tableconstraints...).
I started from the table dbc.columns to get all the info about every field of a table.
Below you can find the work in progress query.

Hi All,
I want to select the current_timestamp in a specific format 'YYYYMMDDHH24MISS'.
Can you please let me know how I should go about it?

Hi Folks, 
Can anyone explain the significance of MODE and FORMAT in Utilities like Fast Export and Multi load ?
And what are the various MODE and FORMAT are avilable at this end ?
I have seen some scenario's where RECORD has been used as MODE.

I want to remove periods from the concatenated string. Please help as I tried all options inlcuding Cast, Format etc.
 
My query is
SEL CONCAT(id, rec_num, line_no, dep_no, asp_num, pr_num) as mystr
from table_oemsp
 
I get the output as
mystr
0.0.300.900.0
11.20.123.123.20
etc.
 

Hi all,

Hi,
I am trying to export data from a table in which one column  has a data type of Decimal(38,0). 
 
Query: select sa from aa;
Result:
When I run the select statement in SQL assistant....the output is fine....like below
 

sa

Hello,

I'm very much new to Teradata and writting queries as such. I have a CHAR Type column which contains data as follows

'2012-01-20 02:32:37.000000'.

How do I convert this CHAR type data to DATE Type, preferably as a timestamp?

Regards,

ASR

What is the best way to convert 'yyyy-mm-dd' to the Teradata interger storage of a date? (yyyy-1900)mmdd

For example:

If my date was April 1, 2012 and it was stored in the database column in the format 'yyyy-mm-dd'.

This is what I am trying to get: 1120401

I have to join two tables using a unique field but the trouble is one table has placed that unique field in all caps and the other table has the field in all lowercase. When joining these two they bring back nothing. When I look up the data individually I can find the same records in both tables.

Hi all,

I have to load some data using Teradata Parallel Transporter...

I have a record like:

1234ANA MARIA       20111209

I need to load something like:

PID: 1234
NAME: ANA MARIA

DATE: 2011-11-09

I expected to do something like:

What is the data format using by TPT\Fastload while primary transfer to Teradata instance?

We using Fixed-width source-files for fasload - its a really excessive data (higly compressible).

Does Fastload (or TPT) use a custom format for transmission to the lower coefficient of redundancy,

Hi,

I  want to use the FORMAT attribute to display the data in a specified format.

Could anybody tell what do the the following format symbols means:

1) (I)

2) N

3) G

Is it possible to get the timestamp to display the timezone like this using SQL?
2011-04-08 09:16:10 PM Central Standard Time

What I have so far:

SQL:
SELECT
current_timestamp as myDefaultTimestamp
, cast(cast(current_timestamp AS TIMESTAMP(6)
FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z') as varchar(40)) as myTimeZone
, cast(
cast(current_timestamp AS TIMESTAMP(6)
FORMAT 'E3,BM4BDD,BY4BHH:MI:SSDS(F)') as varchar(40)) as myTimeZone2

RESULTS:
myDefaultTimestamp
2011-04-08 21:16:10.110000+00:00

myTimeZone
2011-04-08 21:16:10.110000

myTimeZone2

This gets the date, how can I get the full timestamp?

SELECT (CAST('1970/01/01' AS DATE FORMAT 'yyyy/mm/dd')) + CAST(my_time/86400 AS INTEGER) AS CONVERTED_TIME
FROM myDatabase.myTable

I have a table with a column that is an integer and has Unix epoch time data.

Hi

Is there a way to convert TIMESTAMP data to ISO 8061 format, so the result will be something like: ' 2011-04-04T12:57:18.530'?

My data:
15-MAR-11 07.54.08.000000 AM
15-MAR-11 07.53.40.000000 AM
15-MAR-11 07.53.04.000000 AM
15-MAR-11 07.52.55.000000 AM
15-MAR-11 07.52.16.000000 AM

My current DDL:
CREATE multiset TABLE dbname.dbtable
(
CREATE_DATE timestamp(6)
);

My current TPT Insert statement:
'INSERT INTO '||@TargetDatabase||'.dbtable
(
CREATE_DATE = :CREATE_DATE (TIMESTAMP, FORMAT ''DD-MMM-YYBHH.MI.SS.S(6)BT'')
);'

The data is being stored in the database table with century of '19' instead of '20'. My data will always be century of '20':

CREATE_DATE
1911-03-15 07:54:08

Can coalesce and format function go together?

I want the output of a numeric field to be displayed in specific format ,and 0 when the value in the column is NULL.

So, I use

SELECT TRIM(COALESCE(Field,0) (FORMAT 'ZZZ,ZZZ,ZZ')) from Table;

where Filed is the column name and Table is table name.

Hi

I created a table with a single column of TIMESTAMP(0) WITH TIME ZONE. When I try to insert value with the following statement

INSERT INTO XOBTTEST (timestmp_with_zone) VALUES ('2010-10-20 14:23:00-05:00' (TIMESTAMP(0) WITH TIME ZONE,FORMAT 'YYYY-MM-DDbHH:MI:SSZ');

I got this error:
3707: Syntax error, expected sothing like ')' or ',' between ")" and the "WITH" keyboard.

When I try the following statement:

INSERT INTO XOBTTEST (timestmp_with_zone) VALUES ('2010-10-20 14:23:00-05:00' (TIMESTAMP(0) ,FORMAT 'YYYY-MM-DDbHH:MI:SSZ');

Hi, could anyone help me with formating a decimal ?

I need to format a decimal(18,2) field with the following rule :