We have a new community! Please visit support.timextender.com
Follow

SQL Snippets

Author: Joseph Treadwell Last updated:
sql snippet SQL Snippet transformation

Note: There is an updated version of this article, which applies to the next generation of TimeXtender.

 

Attached to this post you will find some SQL snippets for use in TimeXtender.

Please note that they are provided as-is and with no guarantee that they will receive updates or work in all future versions of the software.

SQL snippets are small pieces of SQL code that can be used throughout your project to speed up development. Below are some examples of what SQL snippets can be used for.

  • Field level transformations
  • Views
  • Stored procedures
  • User Defined functions
  • Script actions

Please be aware that it is the content of the individual SQL snippet that determines its suitable usage.

In the attached XML file you will find the following snippets:

Data and Time:

  • Current date - First date of future year
  • Current date - First date of last year
  • Current date - First date of month
  • Current date - First date of previous year
  • Current date - First date of year
  • Current date - Last date of month
  • DateDiff (variable end date)
  • DateDiff (fixed end date)
  • DateKey: Converts a datetime formatted field into an integer field for use as a better performing foreign key.
  • First date of future year
  • First date of last year
  • First date of month
  • First date of next year
  • First date of previous year
  • First date of year
  • Last date of month
  • Time Table: Script action that populates a custom time dimension with 15 minute increments 

Concatenate:

  • Concatenate 2 Fields: Concatenates two fields separated by a space.
  • Concatenate 3 Fields: Concatenates three fields separated by a space. 

Please use Snippet Pack v3.0 for TimeXtender version 6024.1 and later

Was this article helpful?
4 out of 4 found this helpful

5 Comments

Date Votes
  • 0
    Avatar
    Thiré Alain

    I am trying to ccreate a snipplet that converts a julian date to a datetime

    The sql code is 

    DECLARE @a int = '2440588';
    DECLARE @Julian decimal(16,5) = 2458228.05929;
     
    SELECT DATEADD(DAY,CONVERT(int,@Julian) - @a, '19700101') +
           DATEADD(HOUR,12,DATEADD(SECOND,(60 * 60 * 24) * (@Julian - convert(int,@Julian)),0));
     
    What I tried is 
     
    create a custom datetime field
    add a transform 
    set it to snipplet with paramter the  decimal value)
     
    I get errors  on the snipplet because there seems to be quiet some limitations. In fact it looks like it is using the snipplet as  a field definition in sql.
     
    Is this possible ? Or can one only use simple things as the examples show 
     
    How can one do this  ? 
     
     
  • 0
    Avatar
    Permanently deleted user

    Hi Thiré

    Field transformations are in fact embedded in the transformation views in Discovery Hub with the purpose of adding values to single fields. In SQL views you cannot use DECLARE and SELECT functions.

    Instead of declaring variables you should define parameters in the SQL Snippet simply by highlighting the part of the sql statement, you want to replace with a value from either a field or a User Defined Function and click the "Add Parameter" button.

    Example:

    In your SQL Snippet transformation you must simply drag in your field or UDF to the parameters:

    Kind Regards

    Ole Engberg

  • 0
    Avatar
    Sajjad Hussain

    Hi, I am trying to use a very simple SQL snippet using AW sample data but the field returning null.

    Here I am trying to extract date from order date which is lookup filed in salesOrderDetail table.

     

     

    Regards,

    Saj.

  • 0
    Avatar
    Ramon Villanueva

    The xml file with the pack it's not valid for TimeXtender 6024.1

    You need change <Type>Sql</Type> by <Type>SQL</Type> in the XML file.

    ;-)

     

    Edited by Ramon Villanueva
  • 0
    Avatar
    Alex Wijnen

    Thank you for your feedback Ramon. A snippet pack for TimeXtender version 6024.1 and later has just been added.

Please sign in to leave a comment.