« How to know when polls are jokes | Main | Steve Forbes is a socialist »


Feed You can follow this conversation by subscribing to the comment feed for this post.

Jon Peltier

What quacks like a duck, but isn't a duck. Sounds like Teradata still needs a little work.

Cody L. Custis

I spent a frustrating afternoon trying to figure out SAS's LAG function (which does not work as one would expect in case of conditional statements). You can read the basic problem (and workaround) in this lovely paper should you even have such problems: www.howles.com/saspapers/CC33.pdf.

I've found that understanding how different languages give help is useful. SAS tends to have a nice paper written at a user's group for whatever problem one might encounter, and a pile of strange actions due to PDV interpretation. R tends to have a listserve where the author of the function tells users to RTFM, and code snippets with other difficult to understand functions.


I don't think the default string representation of a date value is always yyyy-mm-dd. There's a dateform setting that can be set to 'integerdate' which is yy/mm/dd. This is probably more likely in environments that are supporting older applications, since recent versions of Teradata default to yyyy-mm-dd.

You can also simply tell Teradata the date format in the CAST statement. This would have saved you some time. One example: http://www.tek-tips.com/viewthread.cfm?qid=1157920

The client display format for dates (mm/dd/yyyy) is a separate matter, as you noted.


To be more explicit, if your string is '07/20/2010' in a field named "my_dt", your cast statement would be:

No string manipulation should be required.


I had a similar problem long long time ago. In this galaxy, though. The same trick you used seemed to fix things. After a while I started to notice some anomalies.

The fix? Use 12:00:00 instead.

Why? DTS.

I hate dates. Especially weeks. Leapweeks.


"we had really no rationale why the database would read datetime but not date. You just do random things when you run out of rational ideas."

The academic subject of "Computer Science" is misnamed because as a field of study, it does not use the scientific method of empirical observation. And yet, much of real software development, which should not properly be called "Computer Science," relies heavily on empirical observation. Discuss.

Kim Terek

I hate it, too, when I have to go to the concatinate expressions to solve one of what I call the "massaging the data" issues. And yes, they always occur and they almost almost mess up my completion time estimates! Ugh! When a VP is breathing down your throat and wants a report by end of day, it is always a three hour hold up like this that makes you late for dinner, isn't it??


Jon: while I have issues with Teradata, this post isn't meant to single out that vendor. Similar issues happen with every vendor in one way or another.

I think Teradata makes a product that hugely favors performance over feature set. For example, if it knows that all dates are in one format and one format only, it doesn't have to waste time dealing with transformations. (Cynics will say this just reallocates the time from data warehouse to the analyst.) Maybe people who know more about data warehouses can correct me if I'm mistaken.

KH: the cast function was the first thing attempted. It didn't work. Even the DBA suggested that we don't process dates within Teradata.

njnnja: maybe some day I'll post my thoughts about "computer science". Good point.

Kim: in this case, I wasn't late for dinner because the servers are reserved for production staff overnight :)

Colin Davies

Did you consider using SQL Server's Integration Services tool? It comes "free" with SQL Server and its purpose in life is moving data in and out of the database.


They say 99% of a soldier's life is boredom and 1% is terror. I guess the message here is not about dates, Teradata or SQLServer but that 99% of a data scientist's work is about data preparation and 1% is .... well, we never did get the 1%, maybe tomorrow.

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment

Your Information

(Name is required. Email address will not be displayed with the comment.)


Link to Principal Analytics Prep

See our curriculum, instructors. Apply.
Business analytics and data visualization expert. Author and Speaker. Founder of Principal Analytics Prep, MS Applied Analytics at Columbia. See my full bio.

Next Events

Oct: 31 Webinar on Data Visualization, online at JMP

Nov: 1 NYU unCOMMON Salon Public Lecture, New York, NY

Nov: 8 Tufts Gordon Institute: A Conversation with Kaiser Fung, Facebook Live

Nov: 8 Tufts TGI Careers & Networking Night panel, Somerville, MA

Nov: 26 Data Visualization New York Meetup, New York, NY

Nov: 27 NYPL Data Analytics Resume Workshop, New York, NY

Nov: 30 Purdue School of Engineering Seminar, West Lafayette, IN

Dec: 1 Purdue Mathematics, Data Science, and Industry Conference, West Lafayette, IN

Past Events

See here

Future Courses (New York)

Summer: Statistical Reasoning & Numbersense, Principal Analytics Prep (4 weeks)

Summer: Applied Analytics Frameworks & Methods, Columbia (6 weeks)

Junk Charts Blog

Link to junkcharts

Graphics design by Amanda Lee


  • only in Big Data