Filter on Date not working

问题:

I am doing a insert into a table, which is working fine. But I want to remove data that is prior the year 1999.

This is what I am doing in the query:

SELECT Transaction_No, Item_no, Item_description, Date, Sales 
FROM Inventory_Table
WHERE
[Date] > '1999-01-01 00:00:00.000'

But it keeps showing prior data in the new table.

I have tried removing the '00:00:00.000' from date but still not working.

What seems to be the issue here?

Thanks for any help!

EDIT:

The Date field is a [Datetime] not null,

My query returnes:

Transaction_No      Item_No     Item_Description    Date                      Sales
001                 019238      Baseball            1900-01-01 00:00:00.000   100
002                 014952      Basketball          1900-01-01 00:00:00.000   250
-----------------------------------------------------------------------------------
254                 012459      Gloves              2005-05-05 00:00:00.000   550
255                 014563      Pants               2005-05-05 00:00:00.000   250

I want to get rid of all data prior to the year 1999. That is transactions 001 and 002 in this example.

Have also tried without no help:

SELECT Transaction_No, Item_no, Item_description, Date, Sales 
FROM Inventory_Table
WHERE
Datepart(year,[Date]) > 1999

回答1:

I am doing a insert into a table, which is working fine. But I want to remove data that is prior the year 1999.

Not sure I understand this correctly but insert adds rows to a table. If you want to remove rows you should use delete.

delete from Inventory_Table
where [Date] < '1999-01-01 00:00:00.000'

回答2:

If this query is to select the rows to remove, your angle bracket is wrong, should be less-than, not greater-than. Also, the WHERE clause goes after the FROM clause, like this:

select Transaction_No,
    Item_no,
    Item_description,
    date,
    Sales
from Inventory_Table
where [Date] < '1999-01-01 00:00:00.000'

回答3:

Try casting it to a date:

WHERE [Date] > CAST('1999-01-01 00:00:00.000' AS DATETIME)

If you still have problems, you could try using the DATEDIFF function instead:

WHERE DATEDIFF(DAY, CAST('1999-01-01 00:00:00.000' AS DATETIME), [Date]) > 0

You can adjust the granularity as needed in the above example. It accepts DAY, HOUR, MINUTE, SECOND, etc.