MediaBin database sql views

rabs

I have a requirement to extract the following information for every asset in mediaBin;

Asset Name, Path, File size, Asset type, Insertion time, last modified and Author


I have used the ReportAssetList view in sql server to retrieve Asset Name, Path, Asset Type and Last modified.

I also used the ReportAssetMetaData view in sql server to retrieve file size, Insertion time and author.

My issues are;

1. I get an epoch data value for insertion time.How do I convert this to human readable date (i.e. mm/dd/yyyy:hh:mm:ss) in sql?
2. I get what I think is binary values for file size. How do I convert this to kb in sql?

Has anyone attempted combining the sql views to retrieve all the 7 values for each asset in sql?

lyman

To get from time from the epoch (yes, that's how it is stored) to a date, try:

dateadd(second, X, '1/1/1970')

where X is the value to be converted.

The "Original File Size" metadata should be displayed as a string value holding the number of bytes.

Cheers,

Lyman Hurd