Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

SQL Query with Actual Date

Hello,   I Have a question. What must I write in the M Statement or SQL Statment to get WHERE explicit Date to NOW Date?   Quelle = Odbc.Query("dsn=mmStatistik", "SELECT * FROM mmstatistik#(I...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago
    SELECT *
FROM mmstatistik
WHERE belegdatum 
BETWEEN {d '01/01/2018'}
AND Date()

    was rellay simple...and a lucky type...

     

    that works and gives me the actual date

     

    Thanks for alle the help and sorry for the circumstances.

richardverburg's avatar
richardverburg
Helper I
7 years ago

What data type has the column belegdatum?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

richardverburg

 

the column has type date...can i change the format in beforehand or during the convert like shown here? or here? or here?

 

  • richardverburg's avatar
    richardverburg
    Helper I
    7 years ago

    If it's the format shouldn't matter. SQL will handle it as date and the format is just a layer. 

     

    Can you try this directly on your database. 

    SELECT *
FROM mmstatistik
WHERE belegdatum 
BETWEEN convert(date, getdate()-10)
AND convert(date, getdate())

     

  • richardverburg's avatar
    richardverburg
    Helper I
    7 years ago

    What is your source database? Not mssql right? that was my assumption

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    that's the error i get when i use that code

     

    Spoiler
    DataSource.Error: ODBC: ERROR [HY000] [SAP][Advantage SQL Engine][ASA] Error 7200:  AQE Error:  State = S0000;   NativeError = 2121;  [SAP][Advantage SQL Engine]Column not found: date -- Location of error in the SQL statement is: 61 (line: 4 column: 17)
    Details:
        DataSourceKind=Odbc
        DataSourcePath=dsn=mmStatistik
        OdbcErrors=Table

     

     

  • richardverburg's avatar
    richardverburg
    Helper I
    7 years ago

    I made a typo this is the correct code:

     

    SELECT *
FROM mmstatistik
WHERE belegdatum 
BETWEEN convert(date, getdate()-10)
AND convert(date, getdate())
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    No still the same error...isn't there a way to change the format of the converting?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    SELECT *
FROM mmstatistik
WHERE belegdatum 
BETWEEN convert(date, format(getdate()-10, 'd', 'de-de')) 
AND convert(date, format(getdate(), 'd', 'de-de'))

     

    still that error :(

  • richardverburg's avatar
    richardverburg
    Helper I
    7 years ago

    Dont use the convert function. The format made a string value of the date so that wont work.

     

    SELECT
    format(getdate()-10, 'd', 'de-de')
    , format(getdate(), 'd', 'de-de')

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    SELECT *
FROM mmstatistik
WHERE belegdatum 
BETWEEN format(getdate()-10, 'd', 'de-de')
AND format(getdate(), 'd', 'de-de')

     

    still...

    Spoiler
    DataSource.Error: ODBC: ERROR [HY000] [SAP][Advantage SQL Engine][ASA] Error 7200:  AQE Error:  State = S0000;   NativeError = 2158;  [SAP][Advantage SQL Engine]Scalar function not found: getdate -- Location of error in the SQL statement is: 60 (line: 4 column: 16)
    Details:
        DataSourceKind=Odbc
        DataSourcePath=dsn=mmStatistik
        OdbcErrors=Table

    guess that GetDate() is not implementet or something :(  

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    SELECT *
FROM mmstatistik
WHERE belegdatum 
BETWEEN {d '01/01/2018'}
AND Date()

    was rellay simple...and a lucky type...

     

    that works and gives me the actual date

     

    Thanks for alle the help and sorry for the circumstances.