taT4Sql | Converting Timestamp to Decimal using Teradata SQL

If you have ever worked in Oracle databases, you might have across DUAL table. It’s considered to be system table (I guess) where you can do some operations, get some values like current date, time, etc. I don’t really remember other uses.

Anyways, moving on to Teradata, I thought of doing the same, i.e. pull current timestamp from system table. However, I never came across any DUAL table till now, instead I came to know that just doing the following will do the needful —

SELECT CURRENT_TIMESTAMP;

Now, here comes the hack for the day, my colleague asked me, whether its possible to convert timestamp into decimal format.

Trick 2 Transform..

I remembered doing some casting few months back. If you look at my previous tricks on Teradata SQL, you might remember too. So, this is how I do it —

SELECT CAST( SUBSTR( CAST(CURRENT_TIMESTAMP AS CHAR(19)),1,10) || SUBSTR( CAST(CURRENT_TIMESTAMP AS CHAR(19)),12,8) AS DECIMAL(18));

See ya later..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s