Convert TimeStamp

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 28 Feb 2006 15:55 GMT

Banging my head on this and it is probably simple....

What is the cleanest way to get today's timestamp in a character string
of 14 without seperators YYYYMMDDHHMMSS?

Ie : 2006-02-28-10.53.37.230256
as
20060228105337

I know I can use SUBSTR but seems like a cludge...

Chris

Reply to this Message

Brian Tkatch - 28 Feb 2006 16:50 GMT

Instead of SUBSTR you could use YEAR(), MONTH(), etc...

Or: REPLACE(TRANSLATE(CHAR(CURRENT TIMESTAMP), '', '.-'), ' ', '')

B.

Reply to this Message

Chris - 28 Feb 2006 18:33 GMT

Hmm, didn't work for me. Errored on TRANSLATE. Maybe I messed up the
quotes. However, you did give me an Idea. Hence, this works:

Substr(
REPLACE(REPLACE( CHAR(CURRENT TIMESTAMP), '-', ''), '.','')
,1,14)

Chris

Reply to this Message

Ian - 09 Mar 2006 00:48 GMT

> Banging my head on this and it is probably simple....
>
[quoted text clipped - 4 lines]
> as
> 20060228105337

cast(bigint(your_timestamp) as char(14))

Reply to this Message