Subtract method problems

[Guest Rosco5737]

I am having some trouble with the subtract method for DateTime datatypes for a database and was looking for a little insight. I am working with a Access database through Microsoft's ODBC using the Microsoft.ODBC.dll. There are two columns in that database, Start time and finish time, that are type DateTime. All I want to do is create another column (results) where I can store the difference of the start time and finish time columns. Doing this:

Results.Expression = "FINISH_TIME.Subtract(START_TIME)"

does not work. It gives me this error: The expression contains undefined function call FINISH_TIME.Subtract(). Am I going about this the wrong way? Can you not use the Subtract() function in a column expression? Is there something specific I should be importing. Any thoughts, suggestions? Thanks

[Guest anand_chine]

Hi

 

Use DateDiff() function which will calculate the diff. u wont have to write any function or like Its builtin function.

 

Anand

[Guest Rosco5737]

Actually, I tried DateDiff first (see code segment below) and it gave me the same error:

The expression contains undefined function call DateDiff().

 

code:

Results.Expression = "DateDiff(DateInterval.hour, FINISH_TIME, START_TIME)"

 

 

Any other thoughts, suggestions? Thanks much.

[Guest anand_chine]

Hi

 

try this one

 

hr=dateDiff("h",Finish_Time,Start_Time)

 

where hr is a variable where u want to store the time in Hours

 

 

Anand

[Guest pradeep]

for hours differnece

hrs= DateDiff("h",StartTime,EndTime)

 

for Mins differnece

Mins= DateDiff("n",StartTime,EndTime)

 

for Seconds differnece

Secs= DateDiff("s",StartTime,EndTime)

 

hope this helps

[Guest Rosco5737]

"h", "n", and "s" are just the shorthand string equivalents of DateInterval.hour, DateInterval.minute, and DateInterval.second. Using either notetation still results in the same undefinded function call error. My code now looks like this:

 

Results.Expression = "DateDiff('h', Finish_time, Start_time)"

 

The string is wrapped in single quotes to differentiate between the column names, however, as mentioned above this results in the same error. DateDiff does work fine if you use two exact dates (ie #mm/dd/yyyy# format) so it must be the references to the column names that is messing up. I'm not sure what else I can do... Any other thoughts, suggestions?

Thanks for the help

[Thinker]

I am lost. What is Results.Expression supposed to be?

[Guest Rosco5737]

Results.Expression is where I am defining an expression for a newly created column called Results. I am trying to set the expression of this column to find the difference between two columns (FINISH_TIME and START_TIME). I keep getting the error message: The expression contains undefined function call FINISH_TIME.Subtract OR The expression contains undefined function call DateDiff(). This is my more of my code:

 

 

'created ODBC connection

Dim JobLogDS As DataSet = New DataSet()

Dim JobLogDT As DataTable = JobLogDS.Tables.Add("JobLog")

adapter.Fill(JobLogDS, "JobLog")

Dim Results As New DataColumn("JOB_TIME", GetType(Long))

'Results.Expression = "DateDiff('h', FINISH_TIME, START_TIME)"

'Results.Expression = "DateDiff(DateInterval.Hour, FINISH_TIME, START_TIME)"

'Results.Expression = "System.DateTime.op_Subtraction(START_TIME, FINISH_TIME)"

'Results.Expression = "FINISH_TIME.Subtract(START_TIME)"

 

JobLogDT.Columns.Add(Results)

JobLogDS.Merge(JobLogDT)

 

This is the rundown of the code. I create an ODBC connection to a Microsoft Access database which works fine. I then create a new DataTable and fill its DataSet with the table "JobLog" from the Access Database. This all works fine as well. I then create the Results DataColumn with the name "JOB_TIME" and type Long (for DateDiff or TimeSpan for Subtract). I then try to find the difference of two columns which are type DateTime (named FINISH_TIME and START_TIME) which are columns from the "JobLog" table. I place the result in the Results column that I just created. Each expression results in the error mentioned above. If I use specific dates, it works fine. It is only when I try and reference these columns that it doesn't work. I have tried other column manipulation (like averaging and suming other columns in JobLog that are not type DateTime) and those work fine.

Finally, I add the column to the DataTable and merge it in with the dataset then display it on a datagrid which works fine.

Do Subtract and DateDiff not work in a DataColumn expression? Can you not manipulate Dates through ODBC? I hope this is enough information to find my error. Thanks again all...

[Thinker]

I get the feeling this is ADO.Net. I don't have a clue what the

Expression property of a DataColumn object might be, or what it

can accept. I do know that ODBC can be quite limiting in what it

allows. It is possible that DateDiff would work with OLE DB but

not ODBC. I just don't know. I am moving this to the ADO.Net

board.

 

[edit]I did look this up in my ADO.Net reference and now have a

clue what the DataColumn and Expression are. I still can't tell

what is supported in an Expression. [/edit]

[Derek Stone]

re: Thinker

 

Take a look in the .NET SDK under System.Data.DataColumn. You'll find the complete list of supported expressions.

[Guest Rosco5737]

Thanks Crazed_Lunatic.

Subtract() and DateDiff() are not supported functions for DataColumn.Expression. The question I have now is, is there any work-around to determine the difference between two dates from columns of a database where the result can then be placed in another column. I can not believe this would not be supported in ADO.NET as this would seem to be quite common (but then again, maybe it's just me). The "-" operator does not work on type DateTime so I'm not sure what (if anything) I can do...

Any last thoughts, suggestions? Thanks all.

[Guest mahadevan]

It is a very common taskProblem.

 

You told that you are using Microsoft Access.

 

Datediff function is the correct function. You can attempt in query editor in microsoft Access.

 

The thing is when you save a data column in Access database using date/time, It saves the date along with the time.

 

You may say that you store the format and it show only time. But internally it stores date along with time. So in Calculating difference in time you have to take date into consideration.

 

Consider the general problem.

 

start time is 11:30 PM

End time is 3:30 AM

 

using time only into consideration it will be wrong.

 

Using datetime function with it will give exact result.

 

 

:p

[Thinker]

The "-" operator does not work on type DateTime so I'm not sure what (if anything) I can do...

Are you sure it doesn't work? The values are just stored as

doubles internally. You are allowed to use convert(), so maybe...

Results.Expression = "Convert(Convert(FINISH_TIME, 'System.Int32') - Convert(START_TIME, 'System.Int32'), 'System.DateTime')"

Edited September 10, 2002 by Thinker

[Guest Rosco5737]

Yes, I'm sure. Doing this:

 

Results.Expression = "FINISH_TIME - START_TIME"

 

gives this error:

 

Additional information: Cannot perform '-' operation on System.DateTime and System.DateTime.

 

Trying your method (as seen above) gives this error:

 

Additional information: Invalid cast from DateTime to Int32.

 

 

However, I was able to successfully take the difference of both FINISH_TIME and START_TIME and place the result in Results by physically taking the DateDiff of each record and manually placing it in the appropriate row of Results as seen below:

 

Dim DataView1 As New DataView(JobLogDT)

Dim count As Integer = DataView1.Count

Dim array(count)

Dim i As Integer = 0

While i < count

array(i) = DateDiff("n", DataView1(i)("START_TIME"), DataView1(i)("FINISH_TIME"))

JobLogDT.Rows(i)("Results") = array(i)

i = i + 1

End While

 

 

This may not be the most effecient code but it does work (the Results column has to be type Double). You would think that a DataColumn.Expression would allow MANY more (if not all) functions and operations than its select few but unfortunatly it doesn't. Casting may still work, I only played with it for a little while, but I need to move on.... Thanks to all for all the help; much appreciated.

[Drstein99]

I have a simular question in this same area:

 

Trying to get a simple "HH:MM:SS" (in STRING format)

 

from these two datetime formats:

 

endtime-starttime

 

 

I know:

 

DateDiff(DateInterval.Second, starttime, endTime)

 

will return the number of seconds, but its the total number of seconds, not the ones leftover from hours and minutes. Please help.

[Derek Stone]

Please start a new thread. It is against forum policy to "hijack" older threads. Thank you.