Converting timestamp to date?

flazaro28 · #1 (**permalink**) 10-24-10, 06:51

I'm trying to convert a string to date format. I'm currently using this formula.

Date(CASE
WHEN Table.date=0 THEN '0001-01-01'
WHEN Table.date=10101 THEN '0001-01-01'
ELSE
(
{fn substring(CHAR(Table.date),1,4)}||'-'||
{fn substring(CHAR(Table.date),5,2)}||'-'||
{fn substring(CHAR(Table.date),7,2)})
END)

But the output also displays the timestamp. How do I strip off the time part from the timestamp?

tonkuma · #2 (**permalink**) 10-24-10, 07:46

Two questions:

1) Is the data type of date string?
You wrote

Quote:

I'm trying to convert a string to date format. ...

2) What language are you using?
There are phrases like "{fn substring(CHAR(Table.date),1,4)}" in your formula.

I think there is no such syntax in SQL.

flazaro28 · #3 (**permalink**) 10-24-10, 07:57

Quote:

Originally Posted by tonkuma

Two questions:

1) Is the data type of date string?
You wrote

The actual date format is Numeric. I'm trying to convert it to date format.

2) What language are you using?
There are phrases like "{fn substring(CHAR(Table.date),1,4)}" in your formula.

I think there is no such syntax in SQL.[/QUOTE]

I'm using SQL...Ignore fn from the second statement.

tonkuma · #4 (**permalink**) 10-24-10, 08:22

Two examples.
(I'm not shure that the examples are right answers for you.)

Code:

------------------------------ Commands Entered ------------------------------
SELECT date_num
     , DATE(CASE date_num
            WHEN 0 THEN
                 '0001-01-01'
            ELSE TRANSLATE('abcd-ef-gh' , DIGITS(date_num) , '__abcdefgh')
            END
           ) AS expression1
     , DATE( TRANSLATE( 'abcd-ef-gh'
                      , DIGITS(date_num + (1 - SIGN(date_num)) * 10101)
                      , '__abcdefgh')
           ) AS expression2
  FROM (VALUES 0 , 10101 , 20101025 ) table(date_num);
------------------------------------------------------------------------------

DATE_NUM    EXPRESSION1 EXPRESSION2
----------- ----------- -----------
          0 0001-01-01  0001-01-01 
      10101 0001-01-01  0001-01-01 
   20101025 2010-10-25  2010-10-25 

  3 record(s) selected.

tonkuma · #5 (**permalink**) 10-24-10, 08:54

Another example:

Code:

------------------------------ Commands Entered ------------------------------
SELECT date_num
     , DATE( TO_DATE(DIGITS(date_num + (1 - SIGN(date_num)) * 10101) , 'ssyyyymmdd')
           ) AS expression3
  FROM (VALUES 0 , 10101 , 20101025 ) table(date_num);
------------------------------------------------------------------------------

DATE_NUM    EXPRESSION3
----------- -----------
          0 0001-01-01 
      10101 0001-01-01 
   20101025 2010-10-25 

  3 record(s) selected.

tonkuma · #6 (**permalink**) 10-25-10, 06:31

This expression is shorter than previous three examples.

Code:

------------------------------ Commands Entered ------------------------------
SELECT date_num
     , DATE( TO_DATE(DIGITS(NVL(NULLIF(date_num , 0) , 10101)) , 'ssyyyymmdd')
           ) AS expression4
  FROM (VALUES 0 , 10101 , 20101025 ) table(date_num)
;
------------------------------------------------------------------------------

DATE_NUM    EXPRESSION4
----------- -----------
          0 0001-01-01 
      10101 0001-01-01 
   20101025 2010-10-25 

  3 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech