SQL, LEFT operator on datetime-CodePudding

Is it possible to use the LEFT() operator in MSSQL on a datetime.

I'm asking because this is my db:

In a SQL query i want now to SELECT only this objects WHERE Rueckmeldetatum = a date in form like (2023-01-27) so my string comperable has no time just a date. But with this SQL Query I'm getting no results:

SELECT TOP (1000) [KNR]
      ,[Rueckmeldedatum]
  FROM [Fertigung].[dbo].[Box1Auswertung]
  WHERE LEFT(Rueckmeldedatum,10) ='2023-01-27'

But normally or what i want to get is the 20th entry from the picture.

CodePudding user response：

You should cast the datetime to date, then compare to a date literal:

SELECT TOP (1000) [KNR], [Rueckmeldedatum]
FROM [Fertigung].[dbo].[Box1Auswertung]
WHERE CAST(Rueckmeldedatum AS date) = '20230127';

Or, better yet, use this sargable version:

SELECT TOP (1000) [KNR], [Rueckmeldedatum]
FROM [Fertigung].[dbo].[Box1Auswertung]
WHERE Rueckmeldedatum >= '20230127 00:00:00' AND
      Rueckmeldedatum < '20230128';

CodePudding user response：

WHERE LEFT(CONVERT(CHAR(19),Rueckmeldedatum,104),10) = '09.01.2023'

CodePudding user response：

You should use this instead of LEFT or casting, then, if the index is available, your query can use this:

SELECT TOP (1000) [KNR]
      ,[Rueckmeldedatum]
  FROM [Fertigung].[dbo].[Box1Auswertung]
  WHERE Rueckmeldedatum >= '2023-01-27' AND Rueckmeldedatum < DATEADD(DAY, 1, '2023-01-27')

Also, make your query from the application parameterized.