Skip to Content
author's profile photo Former Member
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 a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on 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 a 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.