6 Replies Latest reply on Feb 13, 2014 7:58 AM by pradeepgm Branched from an earlier case.

    How can I convert BigInt dates into timestamps?

    allanlewis67

      Hi Ed Snajder

       

      1) I was looking for a good place here to post a SQL query I wrote.  When I search for SQL or database there doesn't seem to be a Place related to this.  I fouind some queries in the https://community.jivesoftware.com/groups/uk-user-group but that's not ideal.

       

      Would you consider arranging for one, or who should I contact?

       

       

      2) I toyed once with a query to try to convert the BigInt type dates into dates I could understand, but did not succeed. Yet.  But since we're nboth here, do you have sql code to do this? Below is my attempt.

       

      Thanks,

      Allan

       

       

      Select

       

      dateadd(S, modificationDate, '1970-01-01'

       

      )

       

       

       

      From

       

       

      [jivelaheycore].[dbo].

       

      [jiveDocVersion]

       

       

       

      /* http://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server */

       

       

       

       

       


        • Re: How can I convert BigInt dates into timestamps?
          sanjayd

          Try this:

          select TO_TIMESTAMP(u.creationdate / 1000) from jiveuser u ;

           

          Thanks,

          Sanjay Dhonde

            • Re: How can I convert BigInt dates into timestamps?
              allanlewis67

              Thank you Sanjay.

               

              When I ruun both your exact query

              and one of mine where I refer to creationdate in another table with it,

              I receive following error:

              Msg 195, Level 15, State 10

               

              'TO_TIMESTAMP' is not a recognized built-in function name.

               

               

               

              This is what I'm using with SQL Server 2012:

               

              Microsoft SQL Server Management Studio      11.0.2100.60

              Microsoft Analysis Services Client Tools      11.0.2100.60

              Microsoft Data Access Components (MDAC)      6.1.7601.17514

              Microsoft MSXML      3.0 4.0 6.0

              Microsoft Internet Explorer      8.0.7601.17514

              Microsoft .NET Framework      4.0.30319.1008

              Operating System      6.1.7601

               

              Any quick thoughts?