1. Balaji Pinjala
  2. PowerBuilder
  3. Friday, 7 August 2020 09:00 AM UTC

 

We are using a find function to find the row number of a particular record. Initially it was used with the first two columns which are string data type, now on an enhancement we need to add two more new datatime columns in the find, but after adding this new columns its not working. any suggestions please..

Code which is not working: 

ls_curr_code = dw_curr_rates_upd_fields.getitemstring( i, "cu_curr_code")
ls_cle_code = dw_curr_rates_upd_fields.getitemstring( i, "cu_cle_code")
ld_value_from = dw_curr_rates_upd_fields.GetItemdatetime(i,'value_date_from')
ld_value_to = dw_curr_rates_upd_fields.GetItemdatetime(i,'value_date_to')

 

ll_row = dw_sheet.find("cu_curr_code='"+ls_curr_code + "' and cu_cle_code='"+ ls_cle_code + ' " and value_date_from=" '+ String(ld_value_from) + ' " and value_date_to=" '+ String(ld_value_to)+ "' ",1, dw_sheet.rowcount( ) )

 

René Ullrich Accepted Answer Pending Moderation
  1. Thursday, 1 July 2021 10:54 AM UTC
  2. PowerBuilder
  3. # 1

mdatetime is already of type datetime. Why do you try to convert datetime to datetime?

This should work for you:

dw_1.find("mdatetime = DateTime(Date('2018-05-04'),Time('07:11:00'))",1, dw_1.rowcount())

 

Explanation:

The datawindow datetime function takes a date and optional a time argument: DateTime ( date {, time } )

If you use datetime function to convert a datetime column the argument is used as a date. So the conversion removes the time part! To check this try:

dw_1.find("DateTime(mdatetime) = DateTime(Date('2018-05-04'), Time ('00:00:00'))",1, dw_1.rowcount())

 

HTH,

René

 

 

Comment
There are no comments made yet.
Géza Bartha Accepted Answer Pending Moderation
  1. Thursday, 1 July 2021 08:00 AM UTC
  2. PowerBuilder
  3. # 2

Hi!

 

This question is important for me. I create a small example.

 

The first and second case is ok, the third and forth are fail  with DateTime datatype, and the fitth is correct with extra computed field and string conversion. 

 

How to compare datetime column?

 

Regard,

Géza. 

Attachments (1)
Comment
There are no comments made yet.
Kevin Ridley Accepted Answer Pending Moderation
  1. Friday, 7 August 2020 11:35 AM UTC
  2. PowerBuilder
  3. # 3

You could also create 2 computed columns that are the string values of your datetime columns (don't have to display them), then use those in the Find, so you're comparing string to string.

Comment
There are no comments made yet.
Benjamin Gaesslein Accepted Answer Pending Moderation
  1. Friday, 7 August 2020 09:49 AM UTC
  2. PowerBuilder
  3. # 4

There are two issues with your code:

1. You've mixed up single and double quotes in a few places

2. you're trying to tell the Find Function to compare a date column to a string value, which won't work

To succeed, you need to convert the string back to a date using the Date() DataWindow expression function:

ll_row = dw_sheet.find("cu_curr_code='"+ls_curr_code + "' and cu_cle_code='"+ ls_cle_code + "' and value_date_from= date('"+ String(ld_value_from) + "') and value_date_to= date('"+ String(ld_value_to) + "')", 1, dw_sheet.rowcount( ) )

 

Note that the output of String(date) without a format string varies depending on your system settings and the date() function might not interpret it correctly. We generally use String(date, 'YYYY-MM-DD') for these purposes.

Comment
  1. René Ullrich
  2. Friday, 7 August 2020 10:05 AM UTC
One more thing:

DateTime columns may also contain a time part. In this case you have to conert to datetime instead of date.
  1. Helpful
  1. Géza Bartha
  2. Thursday, 1 July 2021 07:56 AM UTC
I've tried to this, but I cannot use DateTime datatype. Tha date datatypz is ok.
  1. Helpful
  1. Kevin Ridley
  2. Friday, 2 July 2021 11:43 AM UTC
As someone mentioned before, you can create 2 computed columns that are string representations of your datetime columns and filter on those using strings.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.