Skip to Content
avatar image
Former Member

SBO: Formatted Search

Hi all

I have a little problem with formatted search function.

I try to write a formatted search in the order document

which translate the docdate field to a string.

for exa. if the date is 20/03/2004- i want to translate it to: March 20, 2004 in another UDF field.

this is the query I am using:

SELECT convert(nvarchar(20), convert(datetime,$[ORDR.DOCDATE],101), 107) and it is not working, does anyone have an idea ???

Mark

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Mar 22, 2004 at 06:44 PM

    From SQL Server Help: "The style parameter of CONVERT provides a variety of date display formats when converting datetime data to char or varchar."

    It will not use your conversion format when translating the string to a date, only the other way.

    Could you use the substring() function to rebuild the string? It might start:

    select convert(nvarchar(20), convert(datetime, substring($[ORDR.DOCDATE], 5, 2) + substring...

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      I don't think it is a bug, in your case you may need to use $[ORDR.DOCDATE.DATE], or as Dierk mentioned, $[$10.1.DATE] is even better.

      Regarding the syntax of $[$10.1.DATE], Dierk also mentioned clearly: The first number is the Item Id in the form, the second one is the Column Id, and the last one is the Data Type. You can enable the debug information through Tools -> User Tools -> Display Debug Information, then put your cursor on any screen element, you will see these information on the status bar.

      Xuechen