Passing Date to the query and getting records

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 2005 Forums

Transact-SQL (2005)

Passing Date to the query and getting records

Author

Topic

venu.pabboju
Starting Member

3 Posts

Posted - 2009-09-02 : 07:03:06

I got a requirement where i pass Time [PERIOD_START_TIME] as input parameter to my Stored Procedure , and i need to get the records which matches this time [PERIOD_START_TIME] and the records should be order by Date
Im facing problem because it was DateTime

Following is my Table Structure

CREATE TABLE [dbo].[PERIOD](
[PERIOD_ID] [int] IDENTITY(1,1) NOT NULL,
[SCHOOL_YEAR] [smallint] NOT NULL,
[PERIOD_CD] [varchar](30) NOT NULL,
[PERIOD_DESC] [varchar](254) NULL,
[PERIOD_START_TIME] [datetime] NULL,
[PERIOD_END_TIME] [datetime] NULL,
[DATE_CREATED] [datetime] NULL,
[DATE_MODIFIED] [datetime] NULL,
[ClassID] [int] NULL,
[SECTION_ID] [int] NULL)

Following is my query

Declare @iPeriodTime datetime
SELECT [PERIOD_ID]
,[SCHOOL_YEAR]
,[PERIOD_CD]
,[PERIOD_DESC]
,convert(varchar,PERIOD_START_TIME,108)as [PERIOD_START_TIME]
,convert(varchar,PERIOD_END_TIME,108)as [PERIOD_END_TIME]
,convert(varchar,PERIOD_START_TIME,106) as StartDate
,convert(varchar,PERIOD_END_TIME,106) as EndDate

FROM [PERIOD] where PERIOD_START_TIME=@iPeriodTime

Please Provide me help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-02 : 07:05:57

What is the problem you are facing?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-02 : 07:07:35

May be you need

where
PERIOD_START_TIME>=dateadd(day,datediff(day,0,@iPeriodTime),0) and
PERIOD_START_TIME<dateadd(day,datediff(day,0,@iPeriodTime),1)

Madhivanan

Failing to plan is Planning to fail

venu.pabboju
Starting Member

3 Posts

Posted - 2009-09-02 : 08:04:36

Sorry I was not clear in previous post
Actually I will be Passing time(only time not date) from the front end and based on this time i need to get all the records from my table irrespective of Date

2009-07-13 10:00:00.000

The one in the red is my time , so ineed to match with this time and get all records irrespective of Dates

Please let me know if u need any further information
I appreciate for the response

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 08:42:59

where dateadd(day,-1 * datediff(day,0,PERIOD_START_TIME),PERIOD_START_TIME) =dateadd(day,-1 * datediff(day,0,@iPeriodTime),@iPeriodTime)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources