Professional Web Applications Themes

How to convert timestamp values to numeric (SQL70) - Microsoft SQL / MS SQL Server

Pedro, How big is your numeric value? It sounds like an outside of range problem. Timestamp is 8 bytes, which means a pretty big number as a numeric, 9,223,372,036,854,775,808 through 9,223,372,036,854,775,807 That means that you should need at least NUMERIC (19) to capture the value. Russell Fields "Pedro Caetano Barros" <pbarrosscopus.com.br> wrote in message news:00e301c3457c$16470440$a501280aphx.gbl... Sirs, We need to get a timestamp field of a table as a number, but as we use SQL70, we're not able to convert the field to bigint type. SQL help says its possible to do implicit conversion between numeric and timestamp values. However, when ...

  1. #1

    Default Re: How to convert timestamp values to numeric (SQL70)

    Pedro,

    How big is your numeric value? It sounds like an outside of range problem.
    Timestamp is 8 bytes, which means a pretty big number as a numeric,
    9,223,372,036,854,775,808 through 9,223,372,036,854,775,807

    That means that you should need at least NUMERIC (19) to capture the value.

    Russell Fields
    "Pedro Caetano Barros" <pbarrosscopus.com.br> wrote in message
    news:00e301c3457c$16470440$a501280aphx.gbl...
    Sirs,

    We need to get a timestamp field of a table as a number,
    but as we use SQL70, we're not able to convert the field
    to bigint type.
    SQL help says its possible to do implicit conversion
    between numeric and timestamp values. However, when
    doing that, the message "Error 8114 - Error converting
    data type varbinary to numeric.".

    Therefore apparently there's no way to solve it.
    Can anybody help us?

    Thanks a lot.

    Pedro Caetano Barros
    São Paulo - Brazil


    Russell Fields Guest

  2. #2

    Default Re: How to convert timestamp values to numeric (SQL70)

    From BOL:
    "A nonnullable timestamp column is semantically equivalent to a
    binary(8) column. A nullable timestamp column is semantically equivalent
    to a varbinary(8) column."

    Once you have converted it to binary(8), you can use substring to split
    it and convert the pieces to integers.

    Hope this helps,
    Gert-Jan


    Pedro Caetano Barros wrote:
    >
    > Sirs,
    >
    > We need to get a timestamp field of a table as a number,
    > but as we use SQL70, we're not able to convert the field
    > to bigint type.
    > SQL help says its possible to do implicit conversion
    > between numeric and timestamp values. However, when
    > doing that, the message "Error 8114 - Error converting
    > data type varbinary to numeric.".
    >
    > Therefore apparently there's no way to solve it.
    > Can anybody help us?
    >
    > Thanks a lot.
    >
    > Pedro Caetano Barros
    > São Paulo - Brazil
    Gert-Jan Strik Guest

  3. #3

    Default Re: How to convert timestamp values to numeric (SQL70)

    Sirs,

    We made a function using a library procedure that converts
    varbinary to char.

    Gert-Jan Strik and Russel Fields,
    Thank you very much!

    Regards,

    Pedro Caetano Barros
    São Paulo - Brazil



    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Pedro Barros Guest

Similar Threads

  1. SQL Numeric (9) - asp convert.
    By Kami in forum ASP Database
    Replies: 2
    Last Post: October 24th, 12:40 PM
  2. Convert a Byte value to Numeric - Possible?
    By Arnold Shore in forum ASP
    Replies: 2
    Last Post: October 9th, 02:26 AM
  3. How to Convert a Byte to its Numeric Value?
    By Arnold Shore in forum ASP
    Replies: 1
    Last Post: October 9th, 01:49 AM
  4. convert UNIX timestamp
    By Jason Normandin in forum Perl / CGI
    Replies: 1
    Last Post: October 4th, 03:07 PM
  5. [PHP] convert mysql datetime to unix timestamp
    By Adam Voigt in forum PHP Programming
    Replies: 1
    Last Post: July 28th, 04:38 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139