Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2008
    Posts
    4

    Unanswered: first 4 number from BIGINT filed

    Hello all,

    I have 1 table in that 3 fields, and 3rd filed is INTIME and data_type is BIGINT

    i am fire one query as follows:
    select INTIME from table_name where ....

    then Output =102390

    But I want Output is 1023 i.e. only first 4 number

    thanks in Advanced.

    with warm Regards
    Deepak
    --;--;<@

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    substr(char(intime),1,4)

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Or int(intime/100)
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Don't even need the int(), really.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jul 2008
    Posts
    4

    Thanks

    Thanks Very much

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by n_i
    Or int(intime/100)
    This will only work if the bigint is between 100000 and 999999. Any other numbers it will fail.

    Andy

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    True, but we're not dealing with just any numbers here: these are time values represented as numbers, if I understand the OP correctly, so the values will always be between 000000 and 235959
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by n_i
    True, but we're not dealing with just any numbers here: these are time values represented as numbers, if I understand the OP correctly, so the values will always be between 000000 and 235959
    If this is true, then it will fail for all times prior to 10 AM. This is 41% of the day.

    Andy

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, I might have misunderstood the problem. I assumed that the value in INTIME represents time in the format of HHMMSS, and Mr. Deepak wants to extract just hours and minutes. If that's the case my approach would work correctly for any value.

    However, if the time representation is somehow different, the SUBSTR() would be the right approach.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The values are stored in a bigint. So 1 AM would be 10000, not 010000 since there are no "leading zeros" on integer data types. But if the values in the field are indead supposed to be time values in the format of hhmmss and the OP does want just the hhmm portion, then dividing by 100 would work.

    Andy

Posting Permissions

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