I have the following code from a SQL website about creating a current week function. Can someone help me convert it to xbasic so I can use it for my shedule? I'm in the baby steps of learning and this looks too complicated for me.
DECLARE @day INT
DECLARE @today SMALLDATETIME
SET @today = CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)
SELECT DATEADD(dd, 1 - @day, @today) Sunday,
DATEADD(dd, 2 - @day, @today) Monday,
DATEADD(dd, 3 - @day, @today) Tuesday,
DATEADD(dd, 4 - @day, @today) Wednesday,
DATEADD(dd, 5 - @day, @today) Thursday,
DATEADD(dd, 6 - @day, @today) Friday,
DATEADD(dd, 7 - @day, @today) Saturday
Above script can be converted in function.
CREATE FUNCTION dbo.udf_DisplayCurrentWeekDateDays
(@today SMALLDATETIME)
RETURNS @WeekDateDay TABLE
(
Sunday SMALLDATETIME,
Monday SMALLDATETIME,
Tuesday SMALLDATETIME,
Wednesday SMALLDATETIME,
Thursday SMALLDATETIME,
Friday SMALLDATETIME,
Saturday SMALLDATETIME
)
AS
BEGIN
DECLARE @day INT
SET @today = CAST(CONVERT(VARCHAR(10), @today, 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)
INSERT INTO @WeekDateDay (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)
SELECT DATEADD(dd, 1 - @day, @today) Sunday,
DATEADD(dd, 2 - @day, @today) Monday,
DATEADD(dd, 3 - @day, @today) Tuesday,
DATEADD(dd, 4 - @day, @today) Wednesday,
DATEADD(dd, 5 - @day, @today) Thursday,
DATEADD(dd, 6 - @day, @today) Friday,
DATEADD(dd, 7 - @day, @today) Saturday
RETURN
END
GO
The function can be execute for any day to return the week information for the week the day belongs.
----Following will return previous week date and days
SELECT *
FROM dbo.udf_DisplayCurrentWeekDateDays(DATEADD(d,-7,GETDATE()))
GO
DECLARE @day INT
DECLARE @today SMALLDATETIME
SET @today = CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)
SELECT DATEADD(dd, 1 - @day, @today) Sunday,
DATEADD(dd, 2 - @day, @today) Monday,
DATEADD(dd, 3 - @day, @today) Tuesday,
DATEADD(dd, 4 - @day, @today) Wednesday,
DATEADD(dd, 5 - @day, @today) Thursday,
DATEADD(dd, 6 - @day, @today) Friday,
DATEADD(dd, 7 - @day, @today) Saturday
Above script can be converted in function.
CREATE FUNCTION dbo.udf_DisplayCurrentWeekDateDays
(@today SMALLDATETIME)
RETURNS @WeekDateDay TABLE
(
Sunday SMALLDATETIME,
Monday SMALLDATETIME,
Tuesday SMALLDATETIME,
Wednesday SMALLDATETIME,
Thursday SMALLDATETIME,
Friday SMALLDATETIME,
Saturday SMALLDATETIME
)
AS
BEGIN
DECLARE @day INT
SET @today = CAST(CONVERT(VARCHAR(10), @today, 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)
INSERT INTO @WeekDateDay (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)
SELECT DATEADD(dd, 1 - @day, @today) Sunday,
DATEADD(dd, 2 - @day, @today) Monday,
DATEADD(dd, 3 - @day, @today) Tuesday,
DATEADD(dd, 4 - @day, @today) Wednesday,
DATEADD(dd, 5 - @day, @today) Thursday,
DATEADD(dd, 6 - @day, @today) Friday,
DATEADD(dd, 7 - @day, @today) Saturday
RETURN
END
GO
The function can be execute for any day to return the week information for the week the day belongs.
----Following will return previous week date and days
SELECT *
FROM dbo.udf_DisplayCurrentWeekDateDays(DATEADD(d,-7,GETDATE()))
GO
Comment