Referencing System Date in SQL Command Editor

[Former Member]

Post Author: Maxim6469

CA Forum: Data Connectivity and SQL

I need to create a SQL statement that uses the system date in a WHERE clause, but all my known functions or keywords do not work. I have already tried the following functions & keywords:

CURRENT_DATE, Today(), Date(), Now(), SYSDATE, and Sysdate()

Are there any other ways to pull the system date into a WHERE clause in SQL Command Editor?

I'm using Crystal Reports XI

Thank-you

[Former Member]

Post Author: amr_foci

CA Forum: Data Connectivity and SQL

http://www.sql-server-helper.com/faq/dates-p01.aspx

you may need to check this link

[Former Member]

Post Author: amr_foci

CA Forum: Data Connectivity and SQL

i dont think its Today() in SQL Server built-in function

why not you try "GETDATE() " instead of "Today()"

good luck

[Former Member]

Post Author: Maxim6469

CA Forum: Data Connectivity and SQL

The error I receive is:

Failed to retrieve data from the database. Details: 42000:[Microsoft][ODBC SQL Server Driver][SQL Server]'Today' is not a recognized built-in function name. [Database Vendor Code: 195 ]

The query is:

SELECT ndhist_amt, ndhist_dt, ndhist_trcd, ndhist_ab, ndhist_tel_nbr FROM viewmiser_nd_history WHERE Month(ndhist_dt) = Month(Today())-1 and Year(ndhist_dt) = Year(Today()-30) and ndhist_amt <>0

Our database vendor if Fidelity.

[Former Member]

Post Author: amr_foci

CA Forum: Data Connectivity and SQL

you can do it, you can use any system data in anywhere of the Add Command SQL query, but its all based on which functions and keywords your dbms supports

whats the error you've? show me the query and the database vendor , i may help you