Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: Converting timestamp to date?

    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?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two questions:

    1) Is the data type of date string?
    You wrote
    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.

  3. #3
    Join Date
    Oct 2010
    Posts
    2
    Quote Originally Posted by tonkuma View Post
    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.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •