Data manipulation in SQL

Question

I need to write a SQL query where I need the data for the year 2014 from May-Dec and complete data (Jan-Dec) of the year 2015. Can anyone help me with this query?

Answer 1

Use following WHERE predicate

-- data (Jan-Dec) of the year 2015
year = 2015  OR
-- data for the year 2014 from May-Dec
year = 2014 and month in ('May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

Adjust the column names and/or litaral values as required. If the month is stored as number you may use month between 5 and 12

Answer 2

One date col:

SELECT columns FROM table WHERE date >= '2015-05-01' AND date < '2016-1-1'

Separate month and year cols:

SELECT columns, CAST( CAST(year AS VARCHAR(4)) + RIGHT('0' + CAST(month AS VARCHAR(2)), 2) + RIGHT('0' + CAST(day AS VARCHAR(2)), 2) AS DATETIME) as date FROM table WHERE date >= '2015-05-01' AND date < '2016-1-1'

Answer 3

Suppose your date column is named DateID, you want to select all of the columns in the table and you're using a table named in MyTable in the default dbo schema. You can use the following T-SQL query to do that, assuming you're on SQL Server. I added the Year and Month functions to the Select so you can inspect them and an optional ORDER BY clause, so the records will be sorted by the Year and Month. Just substitute the names of your schema, table and date column (you may need to tweak the code a little if you're using Oracle or MySQL or whatever). I hope that helps.

    SELECT Year(DateID), Month(DateID), * 
    FROM dbo.MyTable
    WHERE Year(DateID) = '2015' OR (Year(DateID) = '2014' AND  Month(DateID) BETWEEN 5 and 12)
    ORDER BY  Year(DateID), Month(DateID)