• State Verified Answer
  • Replies 5 replies
  • Answers 1 answer
  • Subscribers 7 subscribers
  • Views 107 views
  • Users 0 members are here
Options
Related

What is the syntax in Freeform SQL to search for a date range?

Amazing Arjan
18 days ago

I'm trying to harness the power of Freeform SQL on the WorldServer, but I can't get the query right.

Is there any documentation available for this?
The only SDL source I've found so far is this post:
https://gateway.sdl.com/apex/communityknowledge?articleName=000011827

This helps, but it only lists some very basic portion of the syntax. I'm looking for something more complete.


To be concrete: How do I search on a date range?
I've tried a lot of variations of this code, but to no avail:

[Created on] > '2020-01-01'

Thanks for any suggestions.
Rate translation
×
Suggest better translation
×
  • +1 18 days ago

    We have someone in our organization that works with this quite often. He gave the response below to address your issue. I do not know where he got the information though.

    Use:

    [Created On] > {1/1/2020}

     

    You have to put the date in curly braces for WS to recognize that it’s a date and treat it accordingly.

    The individual was btw.
  • +1 18 days ago in reply to Eric Bishop

    You can find the info in the WorldServer Online Help, under "Using Translation Memories and Terminology Databases" > "Translation Memories" > "Individual Translation Memory Page" > "Searching for TM Entries" > "The procedure for searching for TM entries" > point #4:

    "4. If you chose the Freeform SQL search mode, type your query (see rules and samples below) and click the Search button () to perform the search.

    Freeform SQL queries have the following rules:

    • Apart from the variable column names and date notation used, everything you type must be in raw SQL format. For example, you must type the character "%" to denote wildcard and the comparison operator "like" to perform containment or substring search.
    • To search for single quote character, use double quote.
    • To search for single backslash character, use double backslash.
    • To search for the SQL wildcard characters "%" and "_", escape them using a backslash.
    • Enclose dates in braces {} using the date format specified in the strings.properties file. For example, MM/dd/yy hh:mm AM (or PM)."

    I don't know if this is documented anywhere in the API documentation, but in my opinion it REALLY should be.
  • 0 18 days ago in reply to Eric Bishop

    Thank you Eric and Ryan for your extremely helpful responses. Based on your feedback, I have taken several actions:

    1- I have submitted a Jira ticket for our development team asking them to review our online documentation (SDL Documentation center) and include information about Freeform SQL. 

    2- I have enhanced article https://gateway.sdl.com/apex/communityknowledge?articleName=000011827 to include the information from the Online Help as pointed out by Ryan.

    3- I have created a new article about Arjan's question and Ryan and Eric's answer: https://gateway.sdl.com/apex/communityknowledge?articleName=000013006

    4- Both articles link to each other. I hope your customers can now find the information they need about Freeform SQL.

    Thanks again for your precious input.
  • 0 17 days ago in reply to Ryan Lee

    Thanks! I have found the entry in the help section. Not at the exact same location, but by searching for "Freeform SQL". The data there will keep me busy for a while. Going to integrate it in an Autohotkey script.
Cookie Preferences