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

SQL Server 2000 Forums

Transact-SQL (2000)

Getdate()

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-07-13 : 06:02:50

Hi Is it possible to used the getdate funcation with a variable.

What i like is to set the getdate() = Number. Where the number can be anything i set it to be.

The reason i want this is, am extractig data and some times the server were am taken the data from can be down, or off line for a few days etc.. and i have allot of scripts where i've set the (getdate() -1) for the pervious day, but i need to reset it everwhere to -7 for example if that server was down for the last 7 days.

Example of script am using is below.

Update RCA
Set Field_Calls = '0'
where RCA.StartTime >= dbo.udf_date_only(getdate() -1)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-13 : 06:06:35

Better redesign your SP so that it will assign value of GetDate() to a datetime local var which can be manipulated the way & the places where you want !

Harsh Athalye
India.
"Nothing is Impossible"

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-07-13 : 06:19:34

Its not a stored procedure, its in DTS packages. Thats were my problem lies..as am allot of SQL TASK..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-13 : 06:29:27

quote:
Originally posted by rookie_sql

Its not a stored procedure, its in DTS packages. Thats were my problem lies..as am allot of SQL TASK..

Then put your getdate() in a Global variable as a initial task and use that global variable anywhere u want GetDate() !

Harsh Athalye
India.
"Nothing is Impossible"

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-07-13 : 07:03:36

Can you give me a example of how to use the global variable as i've never use one before.

Here is my code

declare @Now datetime
set @Now = (getdate()-1)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-13 : 07:04:11

You need to use a variable to set the number of days to extract not to set the system date.
Where you have getdate()-1 you need to be able to change it to getdate()-7 - the 1/7 is the thing that needs changing not getdate().

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-07-13 : 07:29:31

Yes the 1/7 are what i need to change, but i do not want to go through all my SQL Task changing the set @Now = (getdate()-1)

Would creating a global variable solve my problem as harsh_athalye mentioned.

Here is what i have at the moment/

declare @Now datetime
set @Now = (getdate()-1)

I just use the @Now variable instead of the getdate() within my code. So i only have to change it once within the SQL Task

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-13 : 10:51:01

Another parameter would work too


Create Procedure dbo.YourProc
(
     @Days int  -- passed into your proc
     ,@Whatever varchar(50)
)

AS

SET NOCOUNT ON

declare @Now datetime
set @Now = (getdate()-@Days)

-- Do whatever else

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources