Checking against 2 date formats

[Former Member]

My main ERP date is in this format,  YYYYMMDD. I have to compare this against YYYY-MM-DD  both are Numbers. Can I handle this in the Record Selection statement?

[abhilash_kumar]

Hi Paul,

It is never a good idea to handle 'field datatype conversions' in the record selection formula.

None of it is passed back to the database for processing - so you won't see the where clause and CR would need to read every record and process everything locally.

If you're reporting directly against Tables/Views, you should create a SQL Expression field instead.

The SQL Expression uses database specific functions, so depending on what database you're reporting against, look-up its documentation to see what function can best convert the numbers to date.

You can then use a date prompt or a static date with the SQL Expression (just like you'd do with a normal database field).

-Abhilash

[former_member205840]

Hi Paul,

Convert both in to one format and compare :

When date is in number format, when you drag it into report it will add decimal places.  so, convert them into into date formats and compare

date(tonumber(left(Totext({@n},"00000000"),4)),Tonumber(mid(Totext({@n},"00000000"),3,2)),Tonumber(right(Totext({@n},"00000000"),2)))

The above will convert number date format to date.

Note : relace @n with your date field and compare in record selection.

Thanks,

Sastry

[Former Member]

Hi Paul,

Please use below in your Record Selection

ToText({ERPTable.DateField},"yyyyMMdd")=Replace(ToText({Table.DateField},"yyyyMMdd"),"-","")

Let me know if this helps.

Regards

Niraj