null date in CLR - 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 2005 Forums

Transact-SQL (2005)

null date in CLR

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-01-15 : 04:39:55

I've got this function that works fine like this:
select dbo.GetMonday(getdate())

but if i do this:
select dbo.GetMonday(null)

I get:
'GetMonday' failed because parameter 1 is not allowed to be null and is not an output parameter

Here is the code. Why is the check for nothing not working?

Public Shared Function GetMonday(ByVal theDate As Date) As System.Data.SqlTypes.SqlDateTime
If theDate = Nothing Then
'Because returning null is not allowed, you have to return a valid date.
'So be careful of passing in null; you will get an undesireable result.
Return Date.Now
Else
Dim tdate As Date
tdate = CDate(Microsoft.VisualBasic.DateAdd("d", (Microsoft.VisualBasic.Weekday(theDate, Microsoft.VisualBasic.FirstDayOfWeek.Monday) * -1) + 1, theDate))
Return New Date(tdate.Year, tdate.Month, tdate.Day)
End If
End Function

Also, is there a way I could return null if null was the param value?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-15 : 04:43:00

Nothing is not equal to NULL.

If IsNull(theDate) Then

E 12°55'05.25"
N 56°04'39.16"

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-01-15 : 04:58:04

U don't have IsNull in .NET. Also, if you try this:
If theDate = System.DBNull.Value Then

You obviously get "System.DBNull can't be converted to date".

Also, when I try this:
use [master]
GO
GRANT EXECUTE ON ASSEMBLY::[UDAs] TO [MyLogin]
GO

I get:
Msg 102, Level 15, State 1, Line 0
Incorrect syntax near 'EXECUTE...'.

Any ideas why?

ANSWER:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101984

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-15 : 05:13:22

also date is a value type and can't be null
here are some samples of date playing in clr if it help:
http://weblogs.sqlteam.com/mladenp/archive/2006/12/16/52754.aspx

try adding WITH RETURNS NULL ON NULL INPUT to your create function.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-01-15 : 05:29:46

Thanks man, that was good to see.
Do you know how i can add "WITH RETURNS NULL ON NULL INPUT" when creating the function from the VS2005 IDE instead of script?

quote:
Originally posted by spirit1

also date is a value type and can't be null
here are some samples of date playing in clr if it help:
http://weblogs.sqlteam.com/mladenp/archive/2006/12/16/52754.aspx

try adding WITH RETURNS NULL ON NULL INPUT to your create function.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-15 : 05:42:07

no idea. i always deployed the clr stuff in ssms like in my example

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources