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

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

Amazing Arjan

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
×
Parents
  • Former Member
    +1 Former Member

    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 in reply to Former Member

    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.
Reply
  • +1 in reply to Former Member

    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.
Children