Forum Discussion
SQL Query with Actual Date
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.
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.
What data type has the column belegdatum?
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())
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
What is your source database? Not mssql right? that was my assumption
that's the error i get when i use that code
SpoilerDataSource.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=TableI made a typo this is the correct code:
SELECT * FROM mmstatistik WHERE belegdatum BETWEEN convert(date, getdate()-10) AND convert(date, getdate())
No still the same error...isn't there a way to change the format of the converting?
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 :(
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')SELECT * FROM mmstatistik WHERE belegdatum BETWEEN format(getdate()-10, 'd', 'de-de') AND format(getdate(), 'd', 'de-de')
still...
SpoilerDataSource.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=Tableguess that GetDate() is not implementet or something :(
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.