Checking for an specific date

Question

I need to update an invoice value that currently "Invoice Date: Due Upon Receipt”. I need to make it read "June 1, 2020". After June 1, 2020 it will need to revert back to read "Due Upon Receipt".

I am trying to figure it out, but I cannot adjust my machine's date settings because they are managed by the company. So I am using a date of February 1, 2020 instead.

I would like to ask if how I am doing my query that returns the desired value, looks correct or if I need to make a change to make it more efficient.

This is what I got:

SELECT 
    CASE 
        WHEN CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))) >= '02/01/2020'
        THEN 'Due Upon Receipt'
        ELSE 'February 1, ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) [Due Date]
    END

I suggest this should be 2 columns. "June 1, 2020" is clearly a date, not a string, and should be stored in a date (or other date time data type) column, not a varchar. Then you should have a different column for the "status"(?), which can have the value NULL, until needed. — Thom A
– Thom A ♦, Commented Feb 7, 2020 at 16:07
Also be careful with date literals. "02/01/2020" might be considered Feb 1st or Jan 2nd depending on localization settings. You should use the ANSI compliant YYYYMMDD for string literals. In your case it would be '20200201'. That string literal will ALWAYS be interpreted correctly regardless of any local settings. — Sean Lange
– Sean Lange, Commented Feb 7, 2020 at 16:12
Here is a sql fiddle to demonstrate the challenges with literal date strings. sqlfiddle.com/#!18/9eecb/73226 — Sean Lange
– Sean Lange, Commented Feb 7, 2020 at 16:16
For an isolated value in a report I think your case expression is probably ok. It is generally not a good idea to format a date value but with only a single language in a single country it is probably ok. — Sean Lange
– Sean Lange, Commented Feb 7, 2020 at 16:20
"this report will always be used in the United States" I work in the UK, and I only have applications that are used by UK users. I have an application that is written by a UK vendor, where I have to set their login language to ENGLISH (not BRITISH) because they pass dates in the format yyyy-MM-dd to a datetime column... (don't get me started on the fact that I specifically asked them if their application was language agnostic, and was told "yes"... We almost a whole day cause of their stupidity). Just because you think everything is American doesn't mean it will be. — Thom A
– Thom A ♦, Commented Feb 7, 2020 at 16:21

Suraj Kumar · Accepted Answer · 2020-02-07 16:25:55Z

2

Is this what you want?

SELECT CASE WHEN GETDATE() >= '2020-02-01'
             THEN 'Due Upon Receipt'
             ELSE CONCAT('February 1, ',YEAR(GETDATE())) 
        END as [Due Date]

Your query uses February for the cut-off, not June, but it is the same idea.

edited Feb 7, 2020 at 16:25

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

answered Feb 7, 2020 at 16:20

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Checking for an specific date

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related