...WHERE DB.LOG_DATE > DATE - INTERVAL '6' MONTH;
 
This has been working for weeks, until today. I wasn't sure what broke my script until I tried dropping the date requirement and then it started working perfectly. 6 month ago is February 30, I am guessing, by TD logic, which doesn't exist.
Any general solutions?
 

Hi,
I have date in dbc.oldpasswords which is 735960 and 736051. I want to convert it to a date field so that i can read it. The format of this column is - integer format '_,__,__,_9'. I have tried to convert it the way we convert an integer date field to a date fied, but i get error that invalid date provided.
--Samir

I am looking to build and Effective Date and Expiration Date utilzing a customers next purchase date in the table without a do loop if possible as I do not have access to buid stored procedures.
Below is an example of current data:
ID          InvoiceDate
ABC123  4/16/2015
ABC123  5/20/2015
ABC123  6/30/2015
 

Hi,
We are using TDLOAD mechanism to load Staging Layer from File. I belive the default DATE and Timestamp format in the input file which has been configured in DBS Utility would be accepted only. e.g. YYYY-MM-DD.

I have a VARCHAR column (CALL_START_DATE_TIME) and it's values are as shown below.

2014-OCT-31 15:52:35

2014-JUN-25 13:53:30

2013-DEC-26 19:56:40

2014-AUG-08 22:43:51

2015-APR-22 03:57:04

I need to apply date wise filter on this column as below:

Hello,
I have time in format HH:MM and date in format mm/dd/yyyy. I want COL1 with time formated as HH:MM:SS and COL2 a combination of COL1 and date in format MM/DD/YYYY HH:MM:SS.
Also, then I need to subtract COL3 (format mm/dd/yyy hh:mm:ss) from COL2.
How can I do this?
 

Good day all,
I have data that consists of 3 months of charges for each line item.
Bill_No                Bill_Month                     Charge
00121       4/10/2015 12:00:00 AM           1300.00
00121       5/10/2015 12:00:00 AM           1300.00
00121      6/10/2015 12:00:00 AM            1400.00
 

Hello, 

I am a new developer in Teradata world and I'm having to take the quotes from a variable to a software returns me.

I have that: a12.Calendar_Dt in (DATE '11/26/2013')

I can manipulate the start and the end of the variable but not the center.

Hi,
Here is my insert statement.

CREATE  VOLATILE TABLE A (ClickLoad_Dt DATE FORMAT 'yyyy/mm/dd');

INSERT INTO A(ClickLoad_Dt)
SELECT MAX(CAST (click_dt AS DATE FORMAT 'yyyy/mm/dd') ) FROM B;

SELECT * FROM A

I get and empty table even though the answer to below query is 5/25/2015

I have bteq .RUN command sandwiched by .OS calls which log the start and end date times of the .RUN. The dates are gotten by calls to UNIX 'date' command. The START date is fine, however, my COMPLETED date is always equal to my START date. I don't see how that's possible because the 'date' command is invoked fresh on the COMPLETED line.

Hi,
 
I am trying to write a macro because I need to run this monthly. I have to run this monthly for different groups of potential, which I want to pass by parameters.
In the past, I used the current_date, and it worked, but now I would like to pass the data as a parameter and use that one, but that doesn't work:

Hi,
While exporting data from Teradata table with MLSCRIPT option to generate mload script too. I am getting below strings p1<96> or p1 instead of DATE/TIME/TIMESTAMP fields in layout section.
.....
p1<96>.FIELD COLUMN1 * DATE;
p1<96>p1<96>.FIELD COLUMN2 * CHAR(1);
.......

Good Morning All,
I've just changed roles. I've got about 4 years experience with MS SQL Server (T-SQL) and my new role uses Teradata.

Hi,
I was suggested by DBA to change in one of tables , the partitioning column from timestamp to date.
In explain plan I can see that in both cases partition prunning takes place.
Is there anybody that is master in partitioning in teradata and can help if there is some logical reasoning for such change ?
 
Regards,

Hi all,
 
I created a table 

create table financial.test

 (empid integer not null, 

 ename char(30), 

hiredate DATE FORMAT 'ddmmyyyy' )

primary index (empid);

and inserted data into it and the data is as fallows

empid ename hiredate

1 101 abc 01-01-2000

Hello everyone,
 I need to get the week of the year from a date, but 1st of Jan being the 1st week. (no matter what day it falls on). How can I get this in Teradata sql? For eg. 6 Jan 2013 is week 2.  Can someone please help?
Thank you so much.
San

Hi,

I am new to Teradata and I am working on query for which I greatly appreciate any help.

i have data in a table as shown below:

date

id

value

1-Jan-13

1

100

2-Jan-13

1

100

3-Jan-13

Our Teradata instances are used as a DW for our DB2 mainframe application.  In those DW tables, NULL values for date fields are instead represented by '1111-11-11'.  I figure this was either done intentionally as part of the design (either mainframe or DW-side) or it was out of necessity due to NULL not being allowed in a DATE field.

Hi All,
Given the following data sorted by Name and Date:
Name      Status      Date
Bob        A           2013-08-01
Bob        A           2013-08-04
Bob        C           2013-09-13
Sally       A           2013-04-17
Sally       C           2013-06-07
Sally       C           2013-07-09

I'm using this MLOAD script:
(...)

CREATE MULTISET TABLE "DB"."TABLE" (FIELD1 TIMESTAMP(0) NOT NULL) ;
.BEGIN IMPORT MLOAD TABLES "DB"."TABLE" ;

.LAYOUT Layout1 INDICATORS ;

.FIELD  FIELD1 * CHAR(19) ;

.DML LABEL LabelA ;

INSERT INTO "DB"."TABLE" (FIELD1)

    VALUES ( :FIELD1 ) ;

Hi,
     I need to filter only those records in a partition that have overlapping dates. Consider the following scenario,
 MBR_KEY START_DATE END_DATE
1 123 1/1/2013 9/8/2013
2 123 9/1/2013 9/8/2013
3 123 9/9/2013 10/8/2013
4 245 1/1/2013 9/8/2013
5 245 9/1/2013 9/8/2013
6 245 9/9/2013 10/8/2013

Hi all

First post. Apologies in advance if this question has been asked to death before and/ or if I'm posting this to the wrong section.

Hi all

First post. Apologies in advance if this question has been asked to death before and/ or if I'm posting this to the wrong section.

Good Day All,
This is my first time posting and have looked for help here before, but can't seem to figure out what I need to do.  So I figured I would reach out and ask for assistance pertaining to my query.
I need to run a query that allows me to pull out data that has the most current date.  Below is the query I am using:

I am pretty new to teradata and know some basic SQL but I find that I am struggling with this problem.
 

Hi All,
I have a date "Mar 1 2013" which i want to load it into a table with date format as 'MMMBDDBYYYY' is there any way i can load this without adding a "0" before the 1 in the given date?
i am using TD14.

Hi,
This is my first post in this forum. I am new to teradata DB. There is a project that I am working on where we are receiving the date in varchar format :
YYYY-MM-DD HH:MI:SS NNNNNN 

Hello,

Hi All,
I have a SP like this
 

Hi
I need to load a file using mload where  the load clm has a data like this 03/29/2013 12:00:00 PM.
select cast(cast('03/29/2013 12:00:00 PM' as timestamp(0) format 'MM/DD/YYYYBHH:MI:SSBT') as varchar(22))
I need it to load as Timestamp rather than varchar
 
Thanks
Balu
 

Hi,
My Source data has only two digit year when i am trying to convert to timestamp
sel cast('12/12/12 00:00:00' as timestamp(0) format 'MM/DD/YYBHH:MI:SS')
o/p
12/12/1912 00:00:00
tried with the current_timestamp(0)
eg:
sel cast(current_timestamp(0) as timestamp(0) format 'MM/DD/YYBHH:MI:SS')

Hi
My Source data has only two digit year when i am trying to convert to timestamp
sel cast('12/12/12 00:00:00' as timestamp(0) format 'MM/DD/YYBHH:MI:SS')
o/p
12/12/1912 00:00:00
tried with the current_timestamp(0)
eg:
sel cast(current_timestamp(0) as timestamp(0) format 'MM/DD/YYBHH:MI:SS')
o/p

Hi

I currently have an SQL code which determines the most recent time that an item was made serviceable.

What I'm now trying to do is to find the first date in which a notification was raised against that item that occured after the serviceable date, and then count the number of days in between.

For example:

Select EmailAddress, CreatedDate
From _ListSubscribers
Where ListID = 10630 AND CreatedDate > (Select DATEADD (day,-90,getdate()))

Hi,

I need to convert date, which is in the format 'MM/DD/YYYY', to the target format 'DD-MMM-YY'. I tried the following query and it worked too.

SELECT UPPER(CAST(CAST(CURRENT_DATE AS DATE FORMAT 'DD-MMM-YY') AS CHAR(9)));

I would like to know if there is any other better way I can get this done.

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

What is the Maximum date and minimum date value accepted by Teradata?

Hi,

In one table of our data warehouse there are 2 columns, "Sceduled Complete Time" and "Actual Complete Time".

Both these columns are in the format of "YYYY/MM/DD HH:MM:SS"

Hi, I have a table called Employee with Join Date and Last Date. This table may also contain future employees. Now I want to write a View to list all employees (current and future; but not previous employees). Can anyone suggest me best Date logic to get this information.

I'm adding up totals within a database and would like to see how the totals change over different time periods.

I'm using this code:

<code>

Select Sum(CASE WHEN Offer_1 = 'Y' THEN 1 ELSE 0 END) as Offer1,

Sum(CASE WHEN Offer_2 = 'Y' THEN 1 ELSE 0 END) as Offer2,

I'm finding an issue I'm having with a Fastload job and a text file with pretty standard dates extremely maddening, and I am sure V12/V13 has changed Fastload's behaviour in some way, as I'm sure what I am trying to do was not this difficult before.

Consider a few rows of the tab-delimited text file:

Hello, does anyone know of an equivalent for the MSSQL data type DATETIME? I cannot seem to find anything in Teradata documentation that indicates such. Using TIMESTAMP is not an option as it is a HEX type.

Thanks

Hi I have a select satatement in stored procedure,

Inthe where condition, i have

b.dt2_month_end =( DATE :THIS_END_DATE - EXTRACT(DAY FROM :THIS_END_DATE))--------statement A
i'm getting some syntax error.(I declared :THIS_END_DATE parameter before begin, so i think no prob with parameter)

if i give hard coded values like

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

Hi all,

I'm after some help with converting strings in to dates

For example, when i tried to convert '01.12.2010' in to date format '2010-12-01'. I keep getting error "Invalid date supplied"?

Can anyone suggest a solution?

Many thanks in advance

Regards

Joe

Hi,

I'm looking for an efficient way to calculate some windowed aggregates over a table with date-stamped records. A simple table would be organized as:

Acct_Nbr Date Purchase
-----------------------------------------------------
101 2010-01-05 $32.95
101 2010-01-11 $55.00
102 2010-01-03 $12.50
101 2010-02-10 $10.00
101 2010-02-10 $15.00
101 2010-02-10 $85.33