Search a Datetime Field by year or month

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

Transact-SQL (2000)

Search a Datetime Field by year or month

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-03 : 15:01:11

Hi all I was wondering how would I create a search that would give me just the month and year. For example I have a field called (Date DATETIME ) and I want to be able to search this field by year and just, I want to be able to put 2005 in and get all the records for 2005 or the month and get all the records for that month. Does that make sense. How would I go about doing that?

CREATE TABLE [dbo].[Revised_MainTable](
	[I/RDocument] [ntext] NULL CONSTRAINT [DF_Revised_MainTable_I/RDocument]  DEFAULT (N'Scanned Report'),
	[IR Number] [nvarchar](100) NOT NULL,
	[Date] [datetime] NULL,
	[Inspector] [nvarchar](50) NULL,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 15:05:15

SELECT * FROM dbo.Revised_mainTable WHERE YEAR(Date)=2005

or
SELECT * FROM dbo.Revised_mainTable WHERE MONTH(Date)=monthvalue here

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-10-03 : 15:36:56

I have this stored procedure where uers can enter a start date and an end date and I want them to be able to look up by year and month as well here's what I have so far, how would I incorporate that in my stored procedure?

ALTER PROCEDURE [dbo].[AdvSrchIRByDateLstNm]
@StartDate datetime = null,
@EndDate datetime = null,
@Enter_SubjLastName nvarchar(10)  = null,
@Enter_IR_Number nvarchar(100) = null
AS 
SELECT      dbo.RevisedSubjects_Table.SubjFirstName, dbo.RevisedSubjects_Table.SubjLastName, dbo.Revised_MainTable.[Violation Type], 
                        dbo.RevisedSubjects_Table.[IR Number], dbo.Revised_MainTable.[I/RDocument], dbo.Revised_MainTable.Date, dbo.Revised_MainTable.Action
FROM          dbo.Revised_MainTable INNER JOIN
                        dbo.RevisedSubjects_Table ON dbo.Revised_MainTable.[IR Number] = dbo.RevisedSubjects_Table.[IR Number]

WHERE  (@StartDate is null or [Date] >= @StartDate) 
AND (@EndDate is null or [Date] <= @EndDate)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources