SQL Query with Actual Date

[Anonymous]

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 <= ")

[richardverburg]

I would use normal SQL code

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

[DaFloDo]

hi @richardverburg,

I totally agree.

regards

florian

[Anonymous]

@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?

[richardverburg]

I miss a quote in the first select {d, '...'}

[Anonymous]

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]

@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]

What data type has the column belegdatum?

[Anonymous]

@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]

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

[Anonymous]

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]

I made a typo this is the correct code:

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

[Anonymous]

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

[richardverburg]

Sure: 

FORMAT ( getdate(), 'd', 'de-de' ) 

 More information: https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-2017

[Anonymous]

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]

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]

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 · Accepted solution]

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]

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

[Anonymous]

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]

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