Search code examples
sql-serversql-server-2008datalength

SQL Server : datalength conversion

I have a table dbo.files with 9 columns that include file_size and created_time and filepath.

Sample values:

  • file_size = 528300
  • created_time = 2012-06-28 09:31:17.610

I have the following query where I'm trying to show the total # of MB have been written to the filesystem 'today' by these files.

select 
    sum(datalength(f.file_size)) / 1048576.0 AS 'Storage Used Today"
from 
    dbo.files AS f
where 
    f.created_time >= CAST(getdate() as DATE)
    and f.created_time < CAST(DATEADD(day, 1, getdate()) as DATE)

The result is '0.173525810'. Is there a way to move that decimal over to show the proper value?

Thanks

Solution

  • SUM(DATALENGTH(x)) tells you the size in bytes of the numeric representation.

    Which isn't what you need.

    For example if the datatype was integer (4 bytes) and you had three rows with none null values in the column it would evaluate to 12 irrespective of the actual numeric contents.

    Just remove the function call.

    sum(f.file_size) / (1024.0 * 1024)

    Will work fine