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

Comments

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.

KH

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.

KH

To be more explicit, if your string is '07/20/2010' in a field named "my_dt", your cast statement would be:
CAST(my_dt as DATE FORMAT 'MM/DD/YYYY')

No string manipulation should be required.

MV

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.

njnnja

"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??

Kaiser

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.

george

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.

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

Working...

Post a comment

Marketing and advertising analytics expert. Author and Speaker. Currently at Vimeo and NYU. See my full bio.

Next Events

Aug: 20 DataViz New York Meetup

Aug: 26 Optimizely Experience, Invited Expert, New York

Past Events

See here

Junk Charts Blog



Link to junkcharts

Graphics design by Amanda Lee

Search3

  • only in Big Data

Community