MSSQL Date formats? from Iteger to date?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: MSSQL Date formats? from Iteger to date?

  1. #1
    Join Date
    Mar 2003
    Posts
    29

    Question MSSQL Date formats? from Iteger to date?

    Hi,

    got a problem converting a date logged by Cisco in integer format to a proper date format.

    The cisco date is logged as 1088706777 which should equate to 01 Jul 2004 13:32:57, and does this in the cisco system.


    Now as I understand it this number should be the number of secons sins the 1/1/1970 and the following query will give you the date

    DECLARE @mydate_sm DATETIME
    SET @mydate_sm = '1/1/70 00:00:00'
    select @mydate_sm + (((1088706777)/60/60)/24)

    This resolves into the following date:

    2004-07-01 00:00:00.000

    which is correct however the time are not there and only the date

    Any ideas?

    Thanks,

    Jacques

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Looks like you're using the wrong DBMS
    OK in Oracle:
    Code:
      1  select to_char(to_date('01-JAN-1970')+(((1088706777)/60/60)/24),'DD-MON-YYYY HH24:MI:SS')
      2* from dual
    
    TO_CHAR(TO_DATE('01-
    --------------------
    01-JUL-2004 18:32:57

  3. #3
    Join Date
    Mar 2003
    Posts
    29
    Great

    anybody know what to do on sql?

  4. #4
    Join Date
    Mar 2003
    Posts
    29
    OK got it.

    this did the trick:

    SELECT convert( CHAR(23),DATEADD(second, 1088706777, '01-JAN-1970'),13)
    OR
    SELECT DATEADD(second, 1088706777, '01-JAN-1970')

    Cheers

    J
    Last edited by jluckhoff; 07-02-2004 at 11:42 AM.

Posting Permissions

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



Click Here to Expand Forum to Full Width