Problem in getdate - 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

General SQL Server Forums

New to SQL Server Programming

Problem in getdate

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-22 : 00:57:54

hi i have to write an sp which selects certain datas from the table and one of the column in it is the "allocated date" which must get the current date

and i have written the sp in this format

create procedure usp_view_task_home
(
@ASSOC_ID VARCHAR(30),
@STATUS VARCHAR(20),
@TASK_ALLOC_DATE DATETIME
)
as
begin
SELECT
ALLO.TASK_ID,
ALLO.TASK_ALLOC_DATE
HIST.PROJ_ID,
HIST.SUBMOD_ID,
HIST.ACTIVITY,
HIST.TASK_CREATE_BY,
ALLO.TASK_DETAILS
FROM
UST_TASK_ALLOCATION_HISTORY ALLO,
UST_TASK_HISTORY HIST
WHERE
ASSOC_ID =@ASSOC_ID
AND ALLO.TASK_ALLOC_DATE =@TASK_ALLOC_DATE
AND HIST.TASK_ID=ALLO.TASK_ID
END

actually this sp must display the task allocated to a person on the current date when he logins to the home page(so i dono how to assign the getdate to the allocated date)

susan

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-22 : 01:10:50

why have @TASK_ALLOC_DATE as an input variable then ?

create procedure usp_view_task_home
(
@ASSOC_ID VARCHAR(30),
@STATUS VARCHAR(20)
--@TASK_ALLOC_DATE DATETIME
)
as
begin
SELECT
ALLO.TASK_ID,
ALLO.TASK_ALLOC_DATE
HIST.PROJ_ID,
HIST.SUBMOD_ID,
HIST.ACTIVITY,
HIST.TASK_CREATE_BY,
ALLO.TASK_DETAILS
FROM
UST_TASK_ALLOCATION_HISTORY ALLO,
UST_TASK_HISTORY HIST
WHERE
ASSOC_ID =@ASSOC_ID
AND convert(varchar,ALLO.TASK_ALLOC_DATE,112) =convert(varchar,getdate(),112)
AND HIST.TASK_ID=ALLO.TASK_ID
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 01:22:51

quote:
Originally posted by sakets_2000

why have @TASK_ALLOC_DATE as an input variable then ?

create procedure usp_view_task_home
(
@ASSOC_ID VARCHAR(30),
@STATUS VARCHAR(20)
--@TASK_ALLOC_DATE DATETIME
)
as
begin
SELECT
ALLO.TASK_ID,
ALLO.TASK_ALLOC_DATE
HIST.PROJ_ID,
HIST.SUBMOD_ID,
HIST.ACTIVITY,
HIST.TASK_CREATE_BY,
ALLO.TASK_DETAILS
FROM
UST_TASK_ALLOCATION_HISTORY ALLO,
UST_TASK_HISTORY HIST
WHERE
ASSOC_ID =@ASSOC_ID
AND convert(varchar,ALLO.TASK_ALLOC_DATE,112) =convert(varchar,getdate(),112)
AND HIST.TASK_ID=ALLO.TASK_ID
END

its not advisable to convert date values to varchar just for stripping off time part. Always preserve the original datatype. For stripping time part use below

DATEADD(dd,DATEDIFF(dd,0,ALLO.TASK_ALLOC_DATE),0) =DATEADD(dd,DATEDIFF(dd,0,getdate()),0)

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-22 : 01:26:29

hi thanks for replying.

Suppose if i had to display the tasks both on the current date as well as some other past or future dates then how to modify it

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 01:43:43

quote:
Originally posted by susan_151615

hi thanks for replying.

Suppose if i had to display the tasks both on the current date as well as some other past or future dates then how to modify it

susan

what all dates will you be interested in. To get past or future dates substract/add required period to current date

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-10-22 : 01:53:07

You must be calling the SP passing in current date for @TASK_ALLOC_DATE. if you are looking for any current/future assigned task, you can use Dateadd function to compare the dates. Did you look into Date functions in BOL?

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-22 : 02:03:51

sorry i cant get u clearly explain me

susan

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-10-22 : 02:10:05

You were checking the table's allocated date against the input parameter in your original query. If you pass in the current date (or whatever) you can use the sp for different dates. Of course, you've to modify the sp as suggested by others for date comparision logic.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 02:19:33

this is what he meant


create procedure usp_view_task_home
(
@ASSOC_ID VARCHAR(30),
@STATUS VARCHAR(20)
@TASK_ALLOC_DATE DATETIME
)
as
begin
SELECT
ALLO.TASK_ID,
ALLO.TASK_ALLOC_DATE 
HIST.PROJ_ID,
HIST.SUBMOD_ID,
HIST.ACTIVITY,
HIST.TASK_CREATE_BY,
ALLO.TASK_DETAILS
FROM
UST_TASK_ALLOCATION_HISTORY ALLO,
UST_TASK_HISTORY HIST
WHERE
ASSOC_ID =@ASSOC_ID
AND DATEADD(dd,DATEDIFF(dd,0,ALLO.TASK_ALLOC_DATE),0) =DATEADD(dd,DATEDIFF(dd,0,@TASK_ALLOC_DATE),0)
AND HIST.TASK_ID=ALLO.TASK_ID
END

the pass various values of dates like

DECLARE @date datetime
--current date
SET @date=getdate()

Exec usp_view_task_home value1,value2,@date

--past date
SET @date=dateadd(mm,-6,getdate())

Exec usp_view_task_home value1,value2,@date

--future date
SET @date=dateadd(mm,10,getdate())

Exec usp_view_task_home value1,value2,@date
...

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-22 : 02:33:38

actually u r executing the sp to some past or future value by considering some dates which are previous to the current date(in the similar manner for future also) but what i need is instead of setting to some value like -6,10 i need it

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 02:37:01

quote:
Originally posted by susan_151615

actually u r executing the sp to some past or future value by considering some dates which are previous to the current date(in the similar manner for future also) but what i need is instead of setting to some value like -6,10 i need it

susan

need it? from where? thats what you've to tell us.

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-22 : 02:41:21

sorry,i need it to display on any of the past or future values

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 02:55:17

quote:
Originally posted by susan_151615

sorry,i need it to display on any of the past or future values

susan

you can . by passing the date which you want data for as a parameter value.

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-22 : 05:20:19

Hi thank u to all i got my sp done properly with all your guidance

susan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-22 : 06:16:44

quote:
Originally posted by visakh16

AND DATEADD(dd,DATEDIFF(dd,0,ALLO.TASK_ALLOC_DATE),0) =DATEADD(dd,DATEDIFF(dd,0,@TASK_ALLOC_DATE),0)

If you are going to screw up index use anyway, do it as little as possible.

AND DATEDIFF(dd, 0, ALLO.TASK_ALLOC_DATE) = DATEDIFF(dd, 0, @TASK_ALLOC_DATE)

or

AND DATEDIFF(dd, ALLO.TASK_ALLOC_DATE, @TASK_ALLOC_DATE) = 0

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-22 : 08:08:17

If you are not going to screw up index use, use

AND ALLO.TASK_ALLOC_DATE>=DATEADD(dd,DATEDIFF(dd,0,@TASK_ALLOC_DATE),0) AND
AND ALLO.TASK_ALLOC_DATE<DATEADD(dd,DATEDIFF(dd,0,@TASK_ALLOC_DATE),1)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources