ugly hex substring -> date -> rolling average ?

[DBAwhosaysNIE]

all,

I have been asked to produce a report which gives the average age of records in each of a set of tables whose timestamp is the rightmost eight characters of a varchar whose value is hex unix time (sec since 1/1/1970). I have been asked to do this as a view or stored proc (re. all in SQL; no external program). before anyone starts flaming me w/how stupid this sounds let me say: a. I didn't design this system (ironically, we paid significant $ for it) and b. I agree. that being said here are my actual ?s:

1. is there a native, inline function for converting hex to decimal? something like: select hex_to_decimal(right(column_name,8))

2. is it as bad an idea to do this all in SQL as I think it is? I have to believe it would be order of magnitude faster to write a little perl program that opens a cursor to the datbaase and just dumps the whole column to a subroutine for hex conversion and age calculation.

any suggestions would be greatly appreciated...

 

[SQLSister]

No I think you are stuck writing your own function, especially since this is only part of a varchar field.

As to which would be more efficient, it is hard to say. It would depend on a lot of factors such as the size of the recordset, the relative busyness of the SQL Server or the machine the perl script is running one etc. The only way to reallly know this would be to use both methods and then see which runs faster.

Questions about posting. See faq183-874