Question: In SQL: add a column based on before or after date?

Question

In SQL: add a column based on before or after date?

Answers 3
Added at 2016-12-28 23:12
Tags
Question

In Oracle SQL Developer, how do I add a column based on whether my date column has a value before or after January 1st, 2010?

For example:

Date
---------
01-JAN-10
01-JUL-15
07-JAN-07 

I would like to see it like this:

Date        on_time
--------------------
01-JAN-10      N
01-JUL-15      Y
07-JAN-07      N

Where N means the value is equal to or later in time than: January 1st, 2010

and Y means the value is before January 1st, 2010.

Is this even possible to do? I have not added a new column in SQL before.

Answers
nr: #1 dodano: 2016-12-28 23:12

You can use a CASE statement for this:

SELECT your_date, 
CASE WHEN your_date > '2010-01-01' THEN 'Y' ELSE 'N'  END "on_time"
FROM your_table

It checks if the date is greater than 2010-01-01 then it will print Y else it will print N

nr: #2 dodano: 2016-12-28 23:12

You can use a Case Statement based on the date column.
For example:

CASE WHEN [your date column] > '01-JAN-10' THEN 'N'
ELSE 'Y'
END

Of course make sure you use the PL/SQL to_date function for string conversion

nr: #3 dodano: 2016-12-28 23:12

you can use virtual column:

alter table <table_name>
add (<column_name> [<data_type>] [generated always] as (<column_expression>) [virtual]);

so, you can use case function to generate your column:

CASE WHEN Date > to_date('2010-01-01', 'yyyy-mm-dd') THEN 'Y' ELSE 'N' END 
Source Show
◀ Wstecz