How can we help?

New post
0

Help using date picker variable in MS SQL query

Avatar
Ed Crawford
None
Follow

I am trying to use variables set in a date-picker control, to filter data in a SQL query.  I have two variables, $StartDate and $EndDate.  The values appear as expected in the Klip, but my SQL query is not returning any data.  I'm assuming it's something in the way the $StartDate and $EndDate variables are passed to SQL???

Date picker:

Klip showing variable values:

 

SQL Query showing how I am referencing the variables:

SELECT <column1>, <column2> FROM <table>

WHERE SalesDate  >= {props.StartDate}
AND SalesDate <= {props.EndDate}

Additionally, in my data source query, I get an error when on the AND....  I 

Date Votes

4 comments

  • 0
    Avatar
    Janice Janczyn

    Hi Ed,

    I suspect the values returned by your date picker are in a format that doesn't match what your SQL query expects. If no other datasources or formulas are using these variables in the current format, edit your date picker, go to the Properties tab and set the Output Format to match the format required for your SQL query.

    If other datasources or formulas are using these variables in the current format, you may need to add hidden UIs to do date format conversion for your SQL query.

    Thanks,
             Janice

  • 0
    Avatar
    Ed Crawford

    Janice,

    That is exactly what happened.  The date picker data is in UNIX format, so I had to convert it in SQL and then compare against the new variables created in SQL, and it works!  Below is the conversion from UNIX format to SQL format that I used in SQL.

    DECLARE @Unixtime BigInt,
    @StartDate Date,
    @EndDate Date

    SET @Unixtime = {props.mCoachStartDate}
    SET @StartDate= dateadd(S, @Unixtime, '1970-01-01')

    SET @Unixtime = {props.mCoachEndDate}
    SET @EndDate= dateadd(S, @Unixtime, '1970-01-01')

  • 0
    Avatar
    Janice Janczyn

    Excellent!  Glad it's working now.

  • 0
    Avatar
    M

    Has this changed? I found this post and used Ed Crawford's code (posted on 1/14/19) and it worked great! (Thanks Ed!)

    As of 2/1/22, I'm missing some data in Klipfolio vs standard SQL. When I run the code in my IDE I get a slightly different data set versus the same code and same dates in Klip. Has anyone else seen that?

Please sign in to leave a comment.