Joshua Java

Posts Tagged ‘query

In SQL Server 2000 you can not use the AVG() function on a datetime datatype field. So how do you get the average for a datetime datatype field?
This is the nasty way to do it:
convert(varchar, convert(datetime, avg(convert(real, end_time - start_time))), 108 ) avg_call_length

  1. In the example I subtracted two fields: start_time and end_time to get the length of how long user is using the system. You probably don’t need these part. If you don’t need it, you simply just put a datetime field in there instead.
  2. Now before using the avg() function I must convert the datetime values into real datatype.
  3. After having the averay in real datatype values, I would convert it back to the datetime datatype.
  4. You’re done upto this part if you’re happy with the datetime datatype. Unfortunately, the application require a time format to be displayed instead. SQL Server 2000 also don’t have the time datatype. So to get a time format I must convert it to a varchar datatype and then use the 108 type to get the time format values.

If you think you’ve got a better way to do this, please feel free to share with me. 🙂


Recently I’ve got a table with a field that the values needed to have an extra character infront of it. Each record must have extra zero infront of it and in the end the total character must be 10 characters long. So if I have a record with code: 12345, it should be transformed to 0000012345. In popular databases like PostgreSQL, MySQL or Oracle you would just call the LPAD() function. But unfortunately SQL Server 2000 hasn’t got it. Hopefully they already have it the latest version of SQL Server.

I couldn’t really find an elegant way to do this in SQL Server and so this is what I came up with:

select reverse(cast( reverse( cast(code as varchar) ) + '0000000000' as char(10) )) as productcode from product

  1. The first thing I did was to cast the record values as varchar using the cast() function to make sure that the data is in character data type.
  2. The next thing I did was to reverse the record values with the reverse() function.
  3. Then I add the extra zero and the end of the reversed record values.
  4. Then I cast the record as char(10) which would trim the un-needed zero behind the record values.
  5. After that I reverse the record values back with the reverse() function.

It’s a bit stupid, so if you think you’ve got a better way to do it in SQL Server, please feel free to share this with me. 🙂