Printing TRUE for dates less than today?-CodePudding

This is regarding date comparisons. I'm unable to work out a query which returns TRUE for all rows where the date is before today's date.

I have a query which returns TRUE if today's date matches a row value from a column containing those dates, though.

SELECT 
    CASE WHEN EXISTS 
    (
SELECT WFD_AttText5, WFD_AttText1
    FROM WFElements
    WHERE WFD_DTYPEID = 83
    )
    THEN 'TRUE'
    ELSE 'FALSE'
END AS 'Match'
FROM WFElements
WHERE WFD_DTYPEID = 83 AND WFD_AttText5 IN
(        
    SELECT
        FORMAT (
        GETDATE(), 'dddd, dd.MM.yy', 'de-de')
    FROM WFElements
)

This returns TRUE for the one date which matches today's date. But how to I return TRUE for all rows less than today? I think my problem is that I have to use IN() and cannot use < at the same time.

EDIT: This query works better but is not always correct in printing TRUE:

SELECT
    WFD_AttText5
AS 'Datum Formularfeld',
    FORMAT
(
    GETDATE
(), 'dddd, dd.MM.yy', 'de-de') AS 'TODAY formattiert',
    'TRUE' AS [Match]
FROM WFElements
WHERE
    WFD_DTYPEID = 83
    AND WFD_AttText5 < FORMAT
(
        GETDATE
(), 'dddd, dd.MM.yy', 'de-de')

This gives correct results (German) such as:

Mittwoch, 25.05.22 | Freitag, 28.10.22 | TRUE
meaning: Mittwoch, 25.05.22 < Freitag, 28.10.22 which is true.

But also wrong ones:

Freitag, 30.12.22 | Freitag, 28.10.22 | TRUE
meaning: It's supposed to be true, that Freitag, 30.12.22 is less than Freitag, 28.10.22 which is not true.

I'm almost there I hope.

CodePudding user response：

This query will return 'TRUE' for every row where WFD_AttText5 (assuming this is a str date with format dd.mm.yy) is less than the current day. For others it will return 'FALSE'.

SELECT
    WFD_AttText5,
    CASE
        WHEN CONVERT(DATE,WFD_AttText5,4) < CONVERT(DATE,GETDATE()) THEN 'TRUE' -- 4 stands for dd.mm.yy format
        ELSE 'FALSE'
    END AS [Match]
FROM WFElements
WHERE WFD_DTYPEID = 83

Or like that if you only want 'TRUE' values :

SELECT
    WFD_AttText5,
    'TRUE' AS [Match]
FROM WFElements
WHERE
    WFD_DTYPEID = 83
    AND CONVERT(DATE,WFD_AttText5,4) < CONVERT(DATE,GETDATE())

Other tips : in EXISTS always use a SELECT 1

Your first query can be re-write as

SELECT 
    'TRUE' AS [Match]
FROM WFElements
WHERE
    WFD_DTYPEID = 83
    AND CONVERT(DATE,WFD_AttText5,4) = CONVERT(DATE,GETDATE())