We are aware of an issue with the Search bar. Please use Advanced search for the time being while we troubleshoot. Thanks for your patience as we work on improving the community!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Datetime filter on SQL input

rmwillis1973
8 - Asteroid

Hi,

 

I have an issue when trying to filter date data in a datetime format in an SQL data input that i'm using.  I want to exclude data before 1st November 2016

 

Field name = dss_update_time

Example date format = 2012-02-28 14:15:12

 

Date filter in SQL input in Alteryx:

When i put in a date filter as the input tool, it doesn’t filter out any data and gives me the following error:

 

Input Data (1)    Error SQLExecute: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.[Microsoft][ODBC SQL Server Driver][SQL Server]The cursor was not declared.

 

A simple > 2016-10-31 works but doesn't filter anyone out!

 

Please can you advise what I should put in the datetime filter or whether I need some sort of PreSQL statement.  Thanks

12 REPLIES 12
RAHULMISHRA
8 - Asteroid

Hi Roland,

 

Please see highlighted yellow field where filter has been applied as '2019012'. I want to replace '2019012' by some code which upon workflow running will always convert current system time into 'YYYY0MM' format and pull data for most recent period. 

RolandSchubert
15 - Aurora
15 - Aurora

SAP HANA? Because of the tree-character period (leading "0" before "mm") I supposed data source to be SAP ...

SQL for HANA is a bit different ... you could try (I'm not really sure):

CONCAT(CONCAT(LEFT(TO_VARCHAR(CURRENT_DATE, 'YYYY/DD/MM'), 4), '0'), RIGHT(TO_VARCHAR(CURRENT_DATE, 'YYYY/DD/MM'), 2))

 

 

Kaizen
5 - Atom

I've been trying to figure this out for 2 years and have had some painful workarounds! Thank you this worked!!!

Labels