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
--;--;<@

substr(char(intime),1,4)

Andy

Or int(intime/100)

Don't even need the int(), really.

Thanks Very much

This will only work if the bigint is between 100000 and 999999. Any other numbers it will fail.

Andy

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

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.

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