Question

sql server where condition using time

  • 21 November 2018
  • 1 reply
  • 1 view

Badge +10

I would like to filter a view based on the current time -1 day. I have the correct date format working in FME, an attribute creator returns the right value - in the format SQL Server requires.

Unfortunately it appears that the string is being parsed.

MS SQL Server Spatial (JDBC) Reader: Executing SQL: SELECT [title], [user_email], [LOGIN], [COMPANY], [request_date], [theme_id_list], [age_min], [age_max], [request_area], [download_status], [ROW_CREATED_DATE] FROM [dbo].[V_SELF_SERVICE_ACTIVITY_REPORT] WHERE [request_date] > CONVERT(DATETIME, '@DateTimeFormat(@DateTimeAdd(@round(@DateTimeNow(),2), -P1D), %Y-%m-%d %H:%M:%S)', 102)

com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.

 

The where condition:

[request_date] > CONVERT(DATETIME, '@DateTimeFormat(@DateTimeAdd(@round(@DateTimeNow(),2), -P1D), %Y-%m-%d %H:%M:%S)', 102)

 

In the attribute creator which returns correctly it is:

@DateTimeFormat(@DateTimeAdd(@round(@DateTimeNow(),2), -P1D), %Y-%m-%d %H:%M:%S)

returning - 2018-11-20 17:09:36.3

 

Any help would be great, thank you

 


1 reply

Badge +10
Have you tried creating the value in the attributecreator first and referencing that in the WHERE statement?

Reply