Need ORACLE SQL expression ... 1

[TomSalvato]

Hi All,

I am writing an SQL Expressin Field that has to compare two datetime fields, but I'm only concerned about the date portion of each field. In crystal format, my expression would look like this ...

date(TABLE1.DATECREATED) = date(TABLE2.DATECREATED)

where both fields are defined as datetime fields. I know that the 'time' portion will never match. I'm hoping it's something simple that I've just never seen before.

As always, appreciate any help!

-TS

 

[SkipVought]

Hi,

Integer(TABLE1.DATECREATED) = Integer(TABLE2.DATECREATED)

 

[SkipVought]

Oops, I think I'm conflating Access with Oracle.

Didn't your posted expression work?

 

[TomSalvato]

Thanks, Skip ... but Oracle doesn't seem to like that Integer function.

I think I may have found what I needed ...

I'm using the following ...

to_date(TABLE1.DATECREATED) = to_date(TABLE2.DATECREATED)

Doing some testing now, and that looks like it works. Can anyone tell me if they see some intrinsically wrong with that statement? Is the 'to_date' function doing something more than just looking at the date portion only of those fields? Again, it looks like it works. Still testing, though.

Again, appreciate the help!

-TS

 

[TomSalvato]

Check that ... the to_date function does NOT work. Any other ideas?

 

[SkipVought]

try

to_char(TABLE1.DATECREATED,'yyyymmdd') = to_char(TABLE2.DATECREATED, 'yyyymmdd')

 

[TomSalvato]

Still doing some testing to confirm, but at first glance, the to_char solution seems to be EXACTLY what I was looking for.

Thanx, Skip! You're a real pro!

-TS