Verify Year - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Verify Year

Author

Topic

cmh
Starting Member

20 Posts

Posted - 2003-01-29 : 18:05:15

I need to delete records from my table that are not in the current year. There are 10 date fields in each record that need to be checked. If any one of them is from the current year I have to keep the record. My date format is MM/DD/YYYY. What is the best way to do this? My page is written in ASP and my table is SQL2000.

Edited by - cindy hunley on 01/30/2003 11:13:09

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-01-29 : 18:50:13

Here's an example. The thing you are looking for is DATEPART(). Might be a good idea to look that up in the Book Online.

 

CREATE TABLE #MyTable(MyDate DATETIME)
INSERT INTO #MyTable(MyDate) VALUES ('1/1/2002')
INSERT INTO #MyTable(MyDate) VALUES ('2/1/2002')
INSERT INTO #MyTable(MyDate) VALUES ('1/1/2003')
INSERT INTO #MyTable(MyDate) VALUES ('2/1/2003')

--Should return 4 rows
--2 for 2002
--2 for 2003
SELECT * FROM #MyTable

DELETE FROM #MyTable
WHERE DATEPART(yy, myDate) <> DATEPART(yy, GETDATE())

--Should now return 2 rows
--2 for 2003
SELECT * FROM #MyTable


DROP TABLE #MyTable

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources