Sunday, March 11, 2012

DateTime from database entry

Hi, I know this is probably very simple but I am pretty new to this and have tried looking but cant seem to get the search criteria right.

I have a database with a storeDate field which is of shortdatetime type. I am connecting to the database (MSSQL) via a stored procedure and returning all the records. I then use the code

foreach (DataRow dr in ds.Tables[0].Rows)
{
DateTime dtTo = DateTime.Now;
DateTime dtFrom = DateTime.Parse(dr["storeDate"].ToString());
TimeSpan diff = dtTo.Subtract(dtFrom);
}

I am basically trying to find out the age of the database entry by subtracting it from the current DateTime so i can delete records over a certain age. The problem (at least one of them!) is retrieving the "storeDate" object from the database and storing it in the dtFrom object. I have tried just assigning it directly asdtFrom = dr["storeDate"]and various other methods but I just don't know enough to assign it! Can anyone help me with this or spot any other mistakes in this process of removing old files automatically.

Greatly Appreciated,Sean.

How are you storing dates in column storeDate? What format of a date and datatype?

int diff=0;

foreach (DataRow dr in ds.Tables[0].Rows)
{
DateTime dtTo = DateTime.Now;

DateTime dtFrom = (DateTime)(dr["storeDate"]);

diff=DateTime.Compare(dtFrom,dtTo)

if (diff<0){

// dtFrom is less than dtTo

} elseif (diff=0){

// dtFrom=dtTo

} elseif (diff > 0){

// dtFrom is greater than dtTo

}

}

Hope this helps. If you used Timespan to compare, then you would need to convert the datetime's into TimeSpans. DateTime.Compare is a better option.

Eric

|||

Hi Eric, sorry it took so long to get back, I was told my post wasto be moderated first and they'd send me an email but didn't!

Thedatabase stores the data as smalldatetime, I set it when the entry ismade simply using getDate() in the stored procedure. The format is08/11/2007 17:38:00


I tried using DateTime dtFrom = (DateTime)(dr["storeDate"]); but it still throws an error (specified cast is not valid). Should that syntax work? Maybe I have made a mistake somewhere else, but it does automatically generate entries in the storeDate field of the type/format above.

Cheers

|||

Hmm, that should work. I dont know, maybe SmallDateTime isn't compatible with DateTime?

|||

Hi,

From your description, it seems that you want to convert the value you got from data reader to the DateTime typed object, right?

If so, I think you should make sure that if the value which stands for the datatime matches the data time format, or if the data filed type which store the value is "DateTime", if so, try to use the following code to achieve your conversion.

DateTime dt = Convert.ToDateTime(string value);

Thanks.

No comments:

Post a Comment