getdate() returns null in stored procedure

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() returns null in stored procedure

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-05-21 : 06:08:53

Hello,

Debugging the following stored procedure, where @Gdate seems to be set to null

CREATE PROCEDURE test AS
declare @Gdate datetime
select @Gdate = getdate()
GO

Not sure why this is, since in Query Analyser I can select getdate() and return the current date ok.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-21 : 06:28:17

you need to add

select @Gdate

to the end of your proc..

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-05-21 : 08:02:10

Hello,

Thanks, that worked. A variation on that question, I am trying to pass @Gdate into a function, but I suspect I am passing a null value since when I debug @Gdate is set to NULL

CREATE PROCEDURE TArrivalsByAGE
@PrevYear1 varchar(4),@CurrYear1 varchar(4)

AS
declare @Gdate datetime
select @Gdate = getdate()

SELECT agegroup, 1 AS Quarter, January AS A, February AS B, March AS C
FROM AgeCrosstabFunc(@PrevYear1,@CurrYear1,@Gdate)

How do I ensure that I pass in the correct value?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-21 : 08:12:08

[code]
SELECT agegroup, 1 AS Quarter, January AS A, February AS B, March AS C
FROM AgeCrosstabFunc(@PrevYear1,@CurrYear1,getdate())
[/code]
Try that...

or you could always set @Gdate by doing
[code]
set @Gdate = (select getdate())
[/code]

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-05-21 : 08:55:38

Hi,

This is weird:

1. set @Gdate = (Select getdate()) is still returning Null

Error:

Server: Msg 241, Level 16, State 1, Procedure TArrivalsByAGE, Line 9
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

2. Using getdate() in the function call gives me an error:

Error 170: "Incorrect syntax near 'getdate'

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-21 : 09:01:36

Does the third parameter of your function expect a datetime or a string??

Don't know why the set isn't working though?!?!

What version of SQL and which SP are you running??

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-05-21 : 11:27:33

Hi,

It expects datetime:

CREATE FUNCTION AgeCrosstabFunc(@PrevYear
varchar(4),@CurrYear varchar(4), @CurrDate datetime)
RETURNS TABLE

Using SQL Server 20000, SP3

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-05-21 : 11:27:51

Hi,

It expects datetime:

CREATE FUNCTION AgeCrosstabFunc(@PrevYear
varchar(4),@CurrYear varchar(4), @CurrDate datetime)
RETURNS TABLE

Using SQL Server 20000, SP3

stephe40
Posting Yak Master

218 Posts

Posted - 2004-05-21 : 11:49:44

Just a thought, try placing a couple print statements thoughout the sp to see. Otherwise you might want to look into using the debugger in the QA.

- Eric

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-05-21 : 12:22:38

Hi,

Actually using the debugger in QA, which is how I found out that @Gdate is being returned as null.

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-05-21 : 12:33:50

Hi,

But let me ask another question. I am actually trying to replicate a function to find age groups which I have in Access:


Public Function Agegrp(age As Integer) As String
Select Case age
Case 0 To 12
    Agegrp = "00 - 12"
Case 13 To 17
    Agegrp = "13 - 17"
Case 18 To 25
    Agegrp = "18 - 25"
Case 26 To 35
    Agegrp = "26 - 35"
Case 36 To 45
    Agegrp = "36 - 45"
Case 46 To 55
    Agegrp = "46 - 55"
Case 56 To 65
    Agegrp = "56 - 65"
Case Is > 66
    Agegrp = "66+"
End Select
End Function

To invoke this function I use Now(), the T-SQL equivalent being getdate, which is where I am running into problems.

Any idea on where I can find an equivalent SQL UDF for Age Groups?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources