CODEWITHSUNDEEP
mysqlsql
user3712610
user3712610

Reputation: 1

Different output based on date in records

In MySQL, I have a table with the following schema:

CREATE TABLE Orders(
    `Order` int,
    `Date` DATE,
    `Amount` int);

How can I get all records, but display the date of those records older than one year as blank and otherwise show the date of the records one year old or younger?

Upvotes: 0

Views: 57

Answers (3)

dodexahedron
dodexahedron

Reputation: 4657

For MSSQL:

SELECT CASE WHEN DATEDIFF(day,[Date],GETDATE()) > 365 THEN NULL ELSE [Date] END AS [Date] FROM yourTable

For MySQL:

SELECT `Order`, (CASE WHEN DATEDIFF(CURDATE(),Date) > 365 THEN NULL ELSE Date END) AS Date, Amount FROM yourTable;

For Oracle:

SELECT CASE WHEN SYSDATE - yourDateColum > 365 THEN NULL ELSE yourDateColumn END FROM yourTable

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The syntax for MySQL is:

select `order`,
       (case when date >= date_add(now(), interval -365 day) as date
        end),
       amount

By the way, it is bad practice to gives tables or columns reserved words. Order is a reserved word. Often, making it plural or something like OrderId solves the problem.

Upvotes: 1

entropic
entropic

Reputation: 1683

You'll want to use dateadd() as well as getdate() in your sql statement.

Something like this

select Order, case when Date <= dateadd(yy, -1, getdate()) then null else Date end as [PastYearOnly], Amount from [OrderTable]

As Arion mentions ... this is for MS SQL. If you have a different database system it might be a bit different

Upvotes: 0

Related Questions