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#(IF)WHERE belegdatum >= {d '01/01/2018} AND <= ")
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.

22 Replies

  • DaFloDo's avatar
    DaFloDo
    Resolver I
    7 years ago

    hi Anonymous,

     

    DateTime.LocalNow() gives you the current DateTime.

    DateTime.Date(DateTime.LocalNow()) results in only the current date.

     

    best regards,

     

    florian

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hey Anonymous,

     

    You can use something like :

     

    Table.SelectRows(#"Name of Previous Step", each [#"Column Name to Filter"] = DateTime.LocalNow())

     

    *Technically it returns a Date/Time value, where TODAY() returns a Date value. The literal closest to TODAY() is Date.From(DateTime.LocalNow()) which you should use rather than DateTime.LocalNow() alone if the column you're filtering is already a plain date type rather than date/time.*

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      And if i want to place it in the SQL Query?

       

      SELECT * 
FROM mmstatistik 
WHERE belegdatum >= {d '01/01/2018'} AND <= DateTime.Date(DateTime.LocalNow())

       

      • DaFloDo's avatar
        DaFloDo
        Resolver I
        7 years ago

        Hi Anonymous,

        try

         

        = "SELECT * 
FROM mmstatistik 
WHERE belegdatum >= {d '01/01/2018'} AND belegdatum <= '" & Date.ToText(DateTime.Date(DateTime.LocalNow())) &"'"

         

         

        Quelle = Odbc.Query
		(
		"dsn=mmStatistik", 
		"SELECT * 
			FROM mmstatistik 
			WHERE belegdatum >= {d '01/01/2018'} 
			AND belegdatum <= '" & Date.ToText(DateTime.Date(DateTime.LocalNow())) &"'"
		)

         

         

        regards

         

        florian

  • richardverburg's avatar
    richardverburg
    Helper I
    7 years ago

    I would use normal SQL code

     

    = "SELECT * 
FROM mmstatistik 
WHERE belegdatum >= {d '01/01/2018'} AND belegdatum <= convert(date, getdate())"
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        richardverburg

        I got a parsing error with WHERE clause? What that did I typed wrong?

        Or is that Convert Statement not useful for the Source Code?