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.
Author |
Topic |
rjbautista20
Starting Member
2 Posts |
Posted - 2013-06-19 : 02:35:45
|
Would like to ask for your help...I would like to convert my MS Access Function to SQL server function.hope you can help...ThanksFunction Paywindows(ByRef Paydate As Date) As Date If Month(Paydate) = 7 And Day(Paydate) <= 13 Then Paydate = Month(Paydate) & "/12/" & Year(Paydate) ElseIf Month(Paydate) = 7 And Day(Paydate) <= 27 Then Paydate = Month(Paydate) & "/26/" & Year(Paydate) ElseIf Month(Paydate) = 7 And Day(Paydate) >= 28 Then Paydate = DateSerial(Year(Paydate), Month(Paydate) + 1, 12)End ifEnd function |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 03:00:27
|
[code]CREATE FUNCTION PayWindows(@PayDate datetime)RETURNS datetimeASBEGINDECLARE @PayDate datetimeSET @PayDate = DATEADD(mm,DATEDIFF(mm,0,@PayDate),CASE WHEN MONTH(@PayDate)= 7 AND DAY(@PayDate)<=13 THEN 11 MONTH(@PayDate)= 7 AND DAY(@PayDate)<=27 THEN 25 MONTH(@PayDate)= 7 AND DAY(@PayDate)>=28 THEN 41 END)RETURN (@PayDate)END[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
rjbautista20
Starting Member
2 Posts |
Posted - 2013-06-19 : 22:36:23
|
Hi visakh16,Thanks for your reply. i follow your suggest but, i'm having errors while executing the case statementIncorrect syntax near the keyword 'return'. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-20 : 00:28:59
|
quote: Originally posted by rjbautista20 Hi visakh16,Thanks for your reply. i follow your suggest but, i'm having errors while executing the case statementIncorrect syntax near the keyword 'return'.
THEN keywords are missing in the CASE Statement...That should be CREATE FUNCTION PayWindows(@PayDate datetime)RETURNS datetimeASBEGINDECLARE @V_PayDate datetimeSET @V_PayDate = DATEADD(MM,DATEDIFF(MM,0,@PayDate), CASE WHEN MONTH(@PayDate)= 7 AND DAY(@PayDate)<=13 THEN 11 WHEN MONTH(@PayDate)= 7 AND DAY(@PayDate)<=27 THEN 25 WHEN MONTH(@PayDate)= 7 AND DAY(@PayDate)>=28 THEN 41 END)RETURN (@V_PayDate)END --Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-20 : 00:41:49
|
Sorry it wasnot THEN...I was missing couple of WHEN clauses------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|