« 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.

The comments to this entry are closed.

Get new posts by email:
Kaiser Fung. Business analytics and data visualization expert. Author and Speaker.
Visit my website. Follow my Twitter. See my articles at Daily Beast, 538, HBR, Wired.

See my Youtube and Flickr.


  • only in Big Data
Numbers Rule Your World:
Amazon - Barnes&Noble

Amazon - Barnes&Noble

Junk Charts Blog

Link to junkcharts

Graphics design by Amanda Lee

Next Events

Jan: 10 NYPL Data Science Careers Talk, New York, NY

Past Events

Aug: 15 NYPL Analytics Resume Review Workshop, New York, NY

Apr: 2 Data Visualization Seminar, Pasadena, CA

Mar: 30 ASA DataFest, New York, NY

See more here

Principal Analytics Prep

Link to Principal Analytics Prep