SQL Server : datalength conversion

Question

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

that could be. Is there a way to make sum of file_size display as MB? — Docjay, Jul 21 '14 at 19:19
why are you using datalength??? And to move the decimal place is pretty basic math. To move to the right you divide by 10, to move to the left you multiply by 10. — Sean Lange, Jul 21 '14 at 19:19
Is your datatype for file_size a numeric datatype or string? — Sean Lange, Jul 21 '14 at 19:20
This will be on a report that will not just be for my eyes only. I was trying to get it to display differently is all. Is there a different function I should be using? — Docjay, Jul 21 '14 at 19:21
Just get rid of the function call entirely. What remains should work. — Martin Smith, Jul 21 '14 at 19:22
then yes as @MartinSmith said, drop the datalength function. Do you understand why? — Sean Lange, Jul 21 '14 at 19:23
@martin - thanks, worked out. That was just too easy...over complicating things there. — Docjay, Jul 21 '14 at 19:23
@MartinSmith - if you post answer I'll accept it. thanks for everyones help. — Docjay, Jul 21 '14 at 19:27

score 2 · Accepted Answer · answered Jul 21 '14 at 19:32

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

SQL Server : datalength conversion

1 Answers1