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.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Now I get a semicolin Expecting error...what a mess did I do :)? Where should the semicolon placed?

 

SELECT *
FROM mmstatistik
WHERE belegdatum {d '01/01/2018'} AND belegdatum <= Convert(date,getdate())

 EDIT: I saw it the >= was missing :)

Anonymous's avatar
Anonymous
Not applicable
7 years ago

richardverburg

 

Still not working but I did a Test like this

 

SELECT *
FROM mmstatistik
WHERE belegdatum 
BETWEEN {d '01/01/2018'} 
AND {d '01/31/2018'}

 

and that worked...but this doesn't

SELECT *
FROM mmstatistik
WHERE belegdatum 
BETWEEN {d '01/01/2018'} 
AND Convert(Date,GetDate())

 

I am just guessing because I place {d ' 09/21/2018'} aswell...what output should Convert(Date,GetDate()) give me? I saw something like this 21.09.2018 in further tests. Can i change the Format in the SQL Statement? I guess it's a local date option or something like that :).

 

EDIT: But i the data colum it's place like 21.09.2018 aswell. And the right Culture Option is selected.

  • 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())