Stored Proc, param defaults for datetime? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Stored Proc, param defaults for datetime?

Author

Topic

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2010-01-19 : 13:35:09

Why can't I do this?



CREATE PROCEDURE dbo.SP_NAME
(
	@T1 DATETIME = '1/1/' + CAST(DATEPART(YEAR,GETDATE) AS VARCHAR(4)),
	@T2 DATETIME = GETDATE()
)
AS
......
......

I get this error: Incorrect syntax near '+'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-19 : 13:37:01

Because it isn't allowed. Do that after the AS.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2010-01-19 : 13:41:40

quote:
Originally posted by tkizer

Because it isn't allowed. Do that after the AS.

Ah yeah that worked, thanks.


ALTER PROCEDURE dbo.sp_NAME
(
	@T1 DATETIME = NULL,
	@T2 DATETIME = NULL
)
AS

IF @T1 IS NULL BEGIN SET @T1 = '1/1/' + CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4)) END
IF @T2 IS NULL BEGIN SET @T2 = GETDATE() END

...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-19 : 13:48:42

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 14:04:30

Note that you are relying on an implicit date cast - which depends on the Locale of your server (which could conceivably change, over time).

My preference would be to use yyyymmdd format, which is the only date format which will implicitly cast unambiguously, but for good measure I've put an explicit CAST in too

:


IF @T1 IS NULL BEGIN SET @T1 = CAST(CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4)) + '0101') AS DATETIME END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources