CODEWITHSUNDEEP
sqldatecoalescenullif
titatovenaar
titatovenaar

Reputation: 309

SQL Change Null date or 1-1-1900 00:00:00 to Current Date

I googled a couple of hours so far, and tried many different things, but somehow what works with others doesn't seem to work on my dataset.

*EDIT: DBMS: SQL Server. + Code below

Let's say this is the dataset:

ID,VALIDFROM,VALIDTO

1,1-1-2012,1-1-1900 00:00:00

2,1-1-2016,1-3-2017 00:00:00

3,1-1-2017,1-3,2018 00:00:00

4,1-1-2017,1-1-1900 00:00:00

5,1-1-2018,1-1-1900 00:00:00

I want to convert the NULL's to Today's date, but whenever I try to do that with NULLIF or COALESCE, SQL returns 1-1-1900 00:00:00.

SELECT
A.CONTRACTLINEID,
A.CONTRACTID AS 'Contract ID',
COALESCE(C.RENTALCOSTTYPEID, 'LEEG') AS 'Component ID',
A.NAME AS 'Component',
A.LINETYPE AS 'Componenttype ID',
B.ENUMITEMLABEL AS 'Componenttype',
A.RENTALOBJECTID AS 'Vastgoed Object ID',
A.VALIDFROM,
--A.VALIDTO, -- 1-1-1900 00:00:00
--COALESCE(A.VALIDTO,Sysdate()) AS VALIDTO, -- ERROR
--COALESCE(A.VALIDTO,GETDATE()) AS VALIDTO, -- 1-1-1900 00:00:00
--COALESCE(A.VALIDTO,CURRENT_DATE) AS VALIDTO, -- ERROR
--NULLIF(A.VALIDTO,GETDATE()) AS VALIDTO, -- 1-1-1900 00:00:00
--NULLIF(A.VALIDTO,CURRENT_DATE) AS VALIDTO, -- ERROR
A.COSTSETTLEMENTID,
A.PRICEPERIODID,
A.DATAAREAID,
C.PRICEPRICEID AS 'Prijs ID',
COALESCE(C.PRICE, '0') AS 'Prijs',
C.FROMDATE AS 'Prijs Vanaf Datum',
C.TODATE AS 'Prijs T/m Datum',
COALESCE(C.NAME, 'LEEG') AS 'Component-Prijs',
D.CONTRACTSTATUS AS 'Contract Status'
FROM MRPMCCONTRACTLINES A
LEFT JOIN MRENUMS B ON
B.ENUMITEMVALUE = A.LINETYPE
AND B.ENUMID = 40021
OUTER APPLY
(
    SELECT  TOP 1 *
    FROM    MRPMCINVOICELINE C
    WHERE   C.CONTRACTLINEID = A.CONTRACTLINEID AND C.DATAAREAID = '1'
    ORDER BY
            C.TODATE DESC
    ) C
LEFT JOIN PMCCONTRACT D ON
D.CONTRACTID = A.CONTRACTID --Following doesn't do anything so far 
UPDATE MRPMCCONTRACTLINES
SET VALIDTO = '01/01/2050' 
WHERE VALIDTO IS NULL 
OR LTRIM(RTRIM(VALIDTO)) = ''

Upvotes: 0

Views: 5720

Answers (2)

Aaron Dietz
Aaron Dietz

Reputation: 10277

COALESCE() replaces NULL with a value so that is what you want. NULLIF() replaces a value with NULL and doesn't apply here.

My guess is you are doing COALESCE(TODATE,'') which will replace NULL with the default datetime of 1-1-1900 00:00:00.

Make sure you are feeding it today's date properly COALESCE(TODATE,GETDATE())

The function to get today's date will vary based on your DBMS. Always tag your question with your DBMS (SQL Server, MySQL, Oracle, etc..) to get the best answers.

Upvotes: 4

Mordechai
Mordechai

Reputation: 745

you shouldn't be using NULLIF, its purpose is to compare values. If you want to substitute a value for a null in a select statement, you would use isnull (for SQL server) or ifnull (for MySQL)

Upvotes: 0

Related Questions