How to convert SQL timestamp to a string in Entity Framework?

Question

I need to retrieve the MAX(timestamp) value from a SQL table into a string variable but can't figure out how.

So if my table/entity is set up like so:

**DEPARTMENTS**
Department  varchar(10) PK
LastUpdated timestamp

What would the Linq query look like? I have tried a few iterations but always get an error.

EDIT: Here is an example of what I tried

 var result = (from d in _context.LOG_Departments
                      select d.LastUpdated).Max().SingleOrDefault();

error: "Cannot implicitly convert type 'byte' to 'string'

EDIT Solution:

public string MaxDepartment()
    {

        CPLinkEntities _context = new CPLinkEntities();
        var results = _context.LOG_Departments.Max(t => t.LastUpdated);
        string hex = BitConverter.ToString(results);
        hex =  hex.Replace("-", "");
        return hex;
    }

@AshReva, I added an example of what I tried. The timestamp is stored as bunary on the SQL server so it looks like I need to also convert it. Just not that familiar with LINQ. — Alan Fisher
– Alan Fisher, Commented Feb 1, 2014 at 19:22
Did you try removing SingleOrDefault()? Max() already gives you single value — dkozl
– dkozl, Commented Feb 1, 2014 at 19:34
Yes, but since the timestamp is stored as a binary on the server I am now getting the cannot convert type byte to string. — Alan Fisher
– Alan Fisher, Commented Feb 1, 2014 at 19:37

pero · Accepted Answer · 2014-02-01 19:44:51Z

2

Timestamp is not da datetime thing:

http://technet.microsoft.com/en-us/library/ms182776.aspx

Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.

You can then use

long timeStampLong = BitConverter.ToInt64(value, 0);

to get a long back (and convert that long into string if you wish).

answered Feb 1, 2014 at 19:44

pero

4,26930 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alan Fisher Over a year ago

Thanks, I am fully aware of what a timestamp is, I am in fact using it for version control and have since asking the question, found and posted my answer as an edit.

pero Over a year ago

OK, but converting into string seems strange. You can have strange characters in that string. Probably bad for displaying. And you probably cannot even compare 2 of those strings for greater then or similar.

Alan Fisher Over a year ago

I'm using it for version control of lookup tables like Departments (which don't change very often). I am passing the string to a webpage and storing it in local storage, then comparing each page load to it to see if anything in the Departments table has changed and if so I refresh the local storage hash of Departments. I'm not displaying the value at all. I'm going to be repeating this on may lookups and now need to figure out how to dynamically pass in the table name so I don't have to repeat the code. Should be a pretty slick way of updating lookup dropdowns. Thanks.

Alan Fisher · Accepted Answer · 2014-02-16 22:39:43Z

2

I figured out how to do the conversion.

public string MaxDepartment() {

    CPLinkEntities _context = new CPLinkEntities();
    var results = _context.LOG_Departments.Max(t => t.LastUpdated);
    string hex = BitConverter.ToString(results);
    hex =  hex.Replace("-", "");
    return hex;
}

answered Feb 16, 2014 at 22:39

Alan Fisher

2,0554 gold badges42 silver badges62 bronze badges

Comments

Ehsan · Accepted Answer · 2014-02-01 19:23:26Z

-1

it could be something like this:

yourDBContext context=new yourDBContext();
var maxTimestamp = context.yourTable.Max(t => t.Timestamp);

I'd prefer to see this for more detail on working with linq.

answered Feb 1, 2014 at 19:23

Ehsan

83410 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to convert SQL timestamp to a string in Entity Framework?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related