Hi,

I'm using LLBLgen 2.6 Final with Oracle 10g. I have a table with a column as TIMESTAMP(6). The generate code found a DateTime. All seems to be ok.

But when I use a query from llblgen the tool generate this one for the parameter: '12/01/2008 00:00:00' In My database I have 12/01/2008 00:00:00,000000. When I execute my query it never found any records.

Could you help me?

Thanks in advance,

Would you please capture the produced query and try tp manually run it against the database to see if it returns any results.

Something similar was discussed here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=9020

Hi,

Thanks for your reply. Here is the generate sql and this one doesn't run on my database:

SELECT DISTINCT "HR"."Employee"."Id", "HR"."Employee"."DateOfBirth" AS "DateOfBirth"
FROM "HR"."Employee" 
WHERE ( ( ( "HR"."Employee"."DateOfBirth" = :DateOfBirth3)))

Parameter: :DateOfBirth3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 31/12/2009 23:00:00.

I have read the post but it doesn't help me anymore.

Thanks for your support,

Best regards,

What do you mean by "it doesn't run"? Did you replace the parameter value in the query?

SELECT DISTINCT "HR"."Employee"."Id", "HR"."Employee"."DateOfBirth" AS "DateOfBirth"
FROM "HR"."Employee" 
WHERE ( ( ( "HR"."Employee"."DateOfBirth" = '31/12/2009 23:00:00')))

Hi,

Yes I replace the parameter as you explain.

Thanks,

So what do you mean by "it doesn't run"?

Hi,

When I replace the parameter I have an ORA-01830 error. When I use a To_Timestamp('31/12/2008 23:00:00','DD/MM/YYYY HH24:MI:SS.ff') I found my record.

Thanks,

Please post your .NET routine you use to fetch the data and to specify predicates and the like.

Hi,

Here is the routine :

public void GetEmployee(DateTime paramDateOfBirth)
{
var query = (from employee in metadata.Employee where employee.DateOfBirth = paramDateOfBirth
select employee);

var res = ((ILLBLGenProQuery)query).Execute<EntityCollection<EmployeeEntity>>().ToList();
}

so, does paramDateOfBirth contain a value for 'time' ? If so, you won't match the value in the db if the time in the timestamp field doesn't match the time value in the paramDateOfBirth variable exactly (i.e. same milliseconds etc.)

(edit) I see from your trace that it does: "23:00:00". This isn't equal to 00:00:00 in the timestamp field (as stated in your first post). Be sure that seconds and milliseconds are equal too. They're not printed in the trace but they do have to have the same value as the row in the db.

Hi,

During the test I have change the value, and I already have the same value in my datetime and in my database including millisecond.

Any idea ?

Thanks in advance,

I still think there's a millisecond error somewhere. The problem is: timestamps are converted by ODP.NET to DateTime instances, so all we can do is send the data as DateTime parameters.

filtering on exact time values is often difficult due to millisecond differences. I'm not sure what you need for the filtering, but if it's date + hour/minute/second, you should set the millisecond value in the datetime value to 0 prior to saving.

Hi,

What I m looking for is getting my data with or without millisecond. The problem is when I save my entity in the database with or without 0 value on millisecond I can't retreive the entity after with a slect statement.

Best regards

Steria wrote:

Hi,

What I m looking for is getting my data with or without millisecond. The problem is when I save my entity in the database with or without 0 value on millisecond I can't retreive the entity after with a slect statement.

Best regards

If you save it WITH milliseconds, it's required that you use a value with the same milliseconds value.

Filtering on exact TIME values can therefore be harder than anticipated. If you are interested in fetching rows based on the date and not the time fragment, consider using a different type than timestamp. You can also use a DBFunctionCall (see documentation about DBFunctionCall() to wrap the value into a To_TimeStamp() call, or consider using a betweenpredicate to be sure the right row is filtered. But again, filtering on time values with milliseconds can give odd results (not only with oracle, also with sqlserver, where you have the EXACT same time value and it still says they're not equal)

Otis wrote:

Steria wrote:

Hi,

What I m looking for is getting my data with or without millisecond. The problem is when I save my entity in the database with or without 0 value on millisecond I can't retreive the entity after with a slect statement.

Best regards

If you save it WITH milliseconds, it's required that you use a value with the same milliseconds value.

Filtering on exact TIME values can therefore be harder than anticipated. If you are interested in fetching rows based on the date and not the time fragment, consider using a different type than timestamp. You can also use a DBFunctionCall (see documentation about DBFunctionCall() to wrap the value into a To_TimeStamp() call, or consider using a betweenpredicate to be sure the right row is filtered. But again, filtering on time values with milliseconds can give odd results (not only with oracle, also with sqlserver, where you have the EXACT same time value and it still says they're not equal)

Hi,

The think is I want to save my data with millisecond but filter data only with second. Is it possible to do that ?

Thanks,

Regards,

Could you please post more information about your question/scenario? (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=7717)

daelmo wrote:

Could you please post more information about your question/scenario? (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=7717)

• SD.LLBLGen.Pro.DQE.Oracle10g.NET20.dll v2.6.8.819
• SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll v2.6.8.911
• SD.LLBLGen.Pro.TypeConverters.dll v2.6
• Odac 10.102.2.20

Scenario: Insert a date with the complete timestamp (include millisecond) Select all data without taking care of millisecond.

Regards,

Sounds like you need to use ORACLE's equivalent of DateDiff - which looks like it may be done using simple date arithmatic. You could create your own dateDiff function in the Oracle DB and use it with a LLBLGEN DBFunction call to wrap the functionality.

Matt