none
String to DateTime RRS feed

  • Question

  • Hello

    In my SQL database I have two columns, AlarmDate and AlarmTime. These two are stored as char(8). In my LINQ query I would like to get all rows with AlarmDate + AlarmTime > Current time + 20 sec. I do not know how to write this in LINQ.... any ideas?

    The following SQL is kind of doing what I want.

    SELECT

     

     *

    FROM TMP

    WHERE

     

    CONVERT(datetime, AlarmDate + AlarmTime) > '2010-09-11 09:00:00:000'

    Thanks!

    Julia

    Friday, September 17, 2010 10:51 AM

Answers

  • You certainly can edit that, however remember: that is autogenerated code, if you make any change in the design surface of the db then it will return to the default state.  You can also make the change in the designer.

    Other things you can do is extend the class because is partial and provide there a method that take that string and return a date with the added bonus that it will not change when the code is refreshed.

    Also although this might be slight overkill you can do custom mapping.

    Sorry that you don't own the db, that is the right place for putting the right type, but we make do with what we have.

    Regards

    • Marked as answer by liurong luo Thursday, September 23, 2010 8:30 AM
    Friday, September 17, 2010 1:01 PM

All replies

  • http://msdn.microsoft.com/en-us/library/bb882655.aspx

    This are the non supported conversions, other than those you are safe

    http://msdn.microsoft.com/en-us/library/bb882657.aspx

    This is regarding datetime supported operations

    I strongly recommend that you put those rows in a correct type.

    Other than that you can map then to DateTime in your linq to sql file and you will get something like this

    var query =from x in mycontext where ((x.AlarmDate+x.AlarmTime)>DateTime.Now.AddSeconds(20)) select x;

    If you dont want to map it then you will have to convert the strings to datetime using some of the provided methods

    Regards

    • Edited by Serguey123 Friday, September 17, 2010 12:34 PM forgot the x
    Friday, September 17, 2010 12:33 PM
  • Thanks for the answer!

    We do not have access to change the database since we are not the owner. We only have a read account so cannot do any changes to the database.

    So, how do I change the mapping in my link to sql file? Is this where I should edit:

     

    [

     

    global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_AlarmTime", DbType="Char(8)")]

     

     

    public string AlarmTime

    {

     

     

    get

    {

     

     

    return this._AlarmTime;

    }

     

     

    set

    {

     

     

    if ((this._AlarmTime != value))

    {

     

     

    this._AlarmTime = value;

    }

    }

    }

     

    Thanks

     

    Friday, September 17, 2010 12:44 PM
  • You certainly can edit that, however remember: that is autogenerated code, if you make any change in the design surface of the db then it will return to the default state.  You can also make the change in the designer.

    Other things you can do is extend the class because is partial and provide there a method that take that string and return a date with the added bonus that it will not change when the code is refreshed.

    Also although this might be slight overkill you can do custom mapping.

    Sorry that you don't own the db, that is the right place for putting the right type, but we make do with what we have.

    Regards

    • Marked as answer by liurong luo Thursday, September 23, 2010 8:30 AM
    Friday, September 17, 2010 1:01 PM
  • I usually write

    var x = (from row in dataContext.Table
    where Convert.ToDateTime(row.String).Date > CompareDate
    select row);
    
    Friday, September 17, 2010 4:20 PM
  • Hello Julia,

    As an alternative, you could select the records in database first and then use Convert.ToDateTime to cast it to datetime type. Can this meet your requirement?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Wednesday, September 22, 2010 6:43 AM