CODEWITHSUNDEEP
datetime
nation161r
nation161r

Reputation: 61

How do I select a date field when NULL is present?

I am trying to select date fields in my query using ISNULL, but when there is a NULL value rather than an actual date it defaults to 1900-01-01 00:00:00 as a result. Basically, I want nothing displayed if the date field is null, and to display what is populated there when it is anything other than null.

Select isnull(date_field,'') from table_name

Upvotes: 0

Views: 10445

Answers (2)

John R
John R

Reputation: 2096

You didn't say which database you're using. You might want to try:

select if(isnull(date_field) or date_field = '1900-01-01 00:00:00', null, date_field) from table_name;

Upvotes: 2

user3113448
user3113448

Reputation: 1

Google translator is well rounded understanding of place you do not know English.

null, then the query if it excluded

=> SELECT DATE_FIELD FROM TABLE_NAME WHERE DATE_FIELD IS NOT NULL

If you want the default value to be null

=> SELECT NVL(DATE_FIELD, '1900-01-01') FROM TABLE_NAME

=> SELECT NVL(DATE_FIELD, TO_CHAR(SYSDATE, 'yyyymmdd HH24:MI:SS')) FROM TABLE_NAME

Upvotes: 0

Related Questions