Need Help Creating T-SQL Function from VBA

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

General SQL Server Forums

New to SQL Server Programming

Need Help Creating T-SQL Function from VBA

Author

Topic

syed087
Starting Member

5 Posts

Posted - 2010-03-23 : 12:55:50

[font=Verdana]Hello Everyone,

I'm really hoping someone can help me. I need to convert a function that I wrote in an Access database using VB to a scalar function in SS2K5 using T-SQL. The function takes any given date and returns the date of the same day (monday, tuesday, etc.) from the prior year. I am using this in a forecasting model.

On a side note, currently in the Access db, this function is called in a query as a parameter defined by the user. So in SQL, I'll need my views to call the new T-SQL function.

This is the function:

Public Function LYDate(d As Date) As Date

Dim thisWd As Integer 'the day of the week d falls on
Dim thisWk As Integer 'the week of th year d falls on
Dim lastWd As Integer '
Dim lastSun As Date
Dim lastYr As Date
Dim finalDt As Date

thisWd = Weekday(d)
thisWk = Format(d, "ww")
lastYr = Format(DateAdd("yyyy", -1, d), "1/01/yyyy")
lastWk = DateAdd("ww", thisWk - 1, lastYr)
lastWd = Weekday(lastWk)
lastSun = DateAdd("d", (lastWd - 1) * -1, lastWk)

FinalDate= DateAdd("d", thisWd - 1, lastSun)
LYDate= FinalDate

End Function

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-23 : 13:13:41

select datename(dw,dateadd(year,-1,getdate()))

EDIT : Replace getdate() with whatever datetime value you want to pass.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 13:20:27

quote:
Originally posted by vijayisonly

select datename(dw,dateadd(year,-1,getdate()))

EDIT : Replace getdate() with whatever datetime value you want to pass.

I dont think its what OP asked. What OP asked was to return date for the same weekday last year.
ie. considering today as 4th Tuesday of March it should return date of 4th Tuesday of march last year.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-23 : 13:24:11

quote:
Originally posted by visakh16

quote:
Originally posted by vijayisonly

select datename(dw,dateadd(year,-1,getdate()))

EDIT : Replace getdate() with whatever datetime value you want to pass.

I dont think its what OP asked. What OP asked was to return date for the same weekday last year.
ie. considering today as 4th Tuesday of March it should return date of 4th Tuesday of march last year.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Could be...OP needs to clarify it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 13:28:42

may be this?


declare @Lastyeardate datetime,@weekno int,@weekday int
SELECT @Lastyeardate=DATEADD(yy,-1,GETDATE()),
@weekno=DATEPART(wk,GETDATE())-DATEPART(wk,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))+1,
@weekday=DATEPART(dw,GETDATE())
SELECT dbo.GetNthWeekday(@Lastyeardate,@weekno,@weekday)

the function GetNthWeekday can be found below

http://visakhm.blogspot.com/2010/02/find-out-particular-occurance-of.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-23 : 13:49:39

So..it is what Visakh has assumed.
Well..he's right....always.

Did you try the function he posted?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 13:50:37

ok. so its what i guessed

see


declare @Lastyeardate datetime,@weekno int,@weekday int
SELECT @Lastyeardate=DATEADD(yy,-1,'03/21/2010'),@weekno=DATEPART(wk,'03/21/2010')-DATEPART(wk,DATEADD(mm,DATEDIFF(mm,0,'03/21/2010'),0))+1,@weekday=DATEPART(dw,'03/21/2010')
SELECT dbo.GetNthWeekday(@Lastyeardate,@weekno,@weekday) AS LastYearDate

output
------------------------
LastYearDate
2009-03-22 00:00:00.000

http://visakhm.blogspot.com/2010/02/find-out-particular-occurance-of.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 14:06:22

you need to create (just copy paste and run) the function body given in link in your db. then call it using code I've posted here. replace hardcoded date value ('03/21/2010') with a variable and pass your required date through it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

syed087
Starting Member

5 Posts

Posted - 2010-03-23 : 14:28:04

It worked perfectly, thank you so much! You guys are great!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 23:55:31

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources