April 10, 2017 at 9:24 am
Hi,
I've got the following code down but it's currently returning the first Monday of February for every year whereas I would like to have that for Saturday. I've changing DATEFIRST but can't do so inside a function. Any ideas ?
CREATE FUNCTION dbo.GetSaturday (@Date DATETIME)
RETURNS DATETIME
AS BEGIN
DECLARE @callDate DATETIME
DECLARE @calcDate DATETIME
DECLARE @firstSat DATETIME
DECLARE @floatDate FLOAT
DECLARE @Year INT
SET @callDate = @Date
SET @Year = YEAR(@callDate)
SET @calcDate = DATEADD(YEAR, @Year - 1900, 31)
SET @firstSat = DATEADD(DAY, (@@DATEFIRST - DATEPART(WEEKDAY, @calcDate) + (8 - @@DATEFIRST) * 2) % 7, @calcDate)
RETURN @firstSat
END
Thanks.
April 10, 2017 at 9:38 am
I would say that a better option would be use a Date Table, and secondly, convert this to a Inline-Table function.
Firstly, have a look Bones of SQL - The Calendar Table. Then, you can build a function along this line of:CREATE FUNCTION dbo.FirstFebSaturday (@Date date)
RETURNS TABLE AS
RETURN(
SELECT TOP 1 [Date]
FROM DimDate
WHERE [Calendar Year] = DATEPART(YEAR, @Date)
AND [Day Name] = 'Saturday'
AND [Calendar Month] = 2
ORDER BY [Date] ASC);
So therefore the following:SELECT [Date]
FROM dbo.FirstFebSaturday (GETDATE())
Would return:Date
2017-02-04
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 10, 2017 at 9:44 am
An inline function is a good idea, but I'll leave it scalar for now so you can just replace your existing function. I would definitely not use I/O to do this though, since it's not necessary, a simple math calc can get the first of a given day for any given month. Also, don't use any local variables since they slow down processing and aren't absolutely needed here.
Edit: Rather than hard-coding it for Feb, you could consider having the month passed in be the month to calc the Sat for. You could even pass in the day of the week as a variable, 0=Mon,1=Tue,...,5=Sat,6=Sun, rather than having to use a separate function for each one. With the calcs below, it's easy enough to calc for any month and any day of the week, without I/O and that works correctly with any @@DATEFIRST setting.
CREATE FUNCTION dbo.GetSaturday (@Date DATETIME)
RETURNS DATETIME
AS BEGIN
RETURN (
SELECT DATEADD(DAY, -DATEDIFF(DAY, 5, feb_07) % 7, feb_07) AS feb_first_sat
FROM ( SELECT DATEADD(DAY, 37, DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date), 0)) AS feb_07 ) AS assign_alias_name
)
END
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 10, 2017 at 10:09 am
Thom A - Monday, April 10, 2017 9:49 AMThanks Scott, didn't really consider using Math logic. Although, I'll admit, not as readable, does perform quicker. 🙂
Thanks. It's not as readable at first, but once you get used to the technique, you'll find it's actually fairly straightforward. There are two main elements:
1) SQL Server uses '19000101' as its base date, day 0, and that date is a Monday. Therefore, 0=Mon,1=Tue,...,6=Sun. If you always use these day values, you become familiar with them rather quickly.
2) The 7th of the month is the last day that a given day of the week can first appear. So, first compute day 7, then back up to the desired day; if the current day is the desired day, don't adjust the day at all.
Therefore, this code:
DATEDIFF(DAY, 5, feb_07) % 7
figures out how many days past the given day of the week the specified date, in this case Feb 07, is, then subtracts that number of days to take the date back to that day. Note that this calc works exactly the same regardless of the @@DATEFIRST or language settings (day name is never used, only a generic day number).
If you prefer, you can add a bit more clarity to the code by defining the day value as "Saturday", viz:
SELECT DATEADD(DAY, -DATEDIFF(DAY, Saturday, feb_07) % 7, feb_07) AS feb_first_sat
FROM ( SELECT DATEADD(DAY, 37, DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date), 0)) AS feb_07, 5 AS Saturday ) AS assign_alias_name
Sometimes I'll do that, but given the function name and the result column name, I didn't feel that was needed here 😉
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 10, 2017 at 10:46 am
Here is another way:
CREATE FUNCTION dbo.GetSaturday (@Date DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN
(
SELECT FirstSat = dates.dt
FROM
(
VALUES
(1)
, (2)
, (3)
, (4)
, (5)
, (6)
, (7)
) Days (dy)
CROSS APPLY
(
SELECT dt = DATEFROMPARTS(YEAR(@Date), 2, dy)
) dates
WHERE DATENAME(WEEKDAY, dates.dt) = 'Saturday'
);
END;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 10, 2017 at 11:22 am
Here's the function to compute any given day for any given month:
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.GetFirstGivenDayOfMonth (@month date, @day tinyint /*0=Mon,1=Tue,...,6=Sun*/)
RETURNS DATETIME
AS BEGIN
RETURN (
SELECT DATEADD(DAY, -DATEDIFF(DAY, @day, day_07_of_month) % 7, day_07_of_month) AS month_first_day
FROM ( SELECT DATEADD(DAY, 6, DATEADD(MONTH, DATEDIFF(MONTH, 0, @month), 0)) AS day_07_of_month ) AS assign_alias_name
)
END
GO
SELECT dbo.GetFirstGivenDayOfMonth ('20170224', 5 /*first Sat in Feb 2017*/)
SELECT dbo.GetFirstGivenDayOfMonth ('20161217', 1 /*first Tue in Dec 2016*/)
SELECT dbo.GetFirstGivenDayOfMonth ('20180101', 3 /*first Thu in Jan 2018*/)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 10, 2017 at 1:24 pm
Thanks all. Worked perfectly. I used Scott's inline example - matched what i needed plus extra bonus explanation.
April 10, 2017 at 2:28 pm
prefet - Monday, April 10, 2017 1:24 PMThanks all. Worked perfectly. I used Scott's inline example - matched what i needed plus extra bonus explanation.
If you are at all worried about performance, I urge you to consider implementing this as an iTVF rather than a scalar function. It will run significantly faster over larger datasets. For example, here is my version, rewritten as an iTVF:
CREATE FUNCTION dbo.GetSaturdayiTVF (@Date DATETIME)
RETURNS TABLE
WITH SCHEMABINDING RETURN
SELECT FirstSat = dates.dt
FROM
(
VALUES
(1)
, (2)
, (3)
, (4)
, (5)
, (6)
, (7)
) Days (dy)
CROSS APPLY
(
SELECT dt = DATEFROMPARTS(YEAR(@Date), 2, dy)
) dates
WHERE DATENAME(WEEKDAY, dates.dt) = 'Saturday';
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 10, 2017 at 2:53 pm
Phil Parkin - Monday, April 10, 2017 2:28 PMprefet - Monday, April 10, 2017 1:24 PMThanks all. Worked perfectly. I used Scott's inline example - matched what i needed plus extra bonus explanation.If you are at all worried about performance, I urge you to consider implementing this as an iTVF rather than a scalar function. It will run significantly faster over larger datasets. For example, here is my version, rewritten as an iTVF:
CREATE FUNCTION dbo.GetSaturdayiTVF (@Date DATETIME)
RETURNS TABLE
WITH SCHEMABINDING RETURNSELECT FirstSat = dates.dt
FROM
(
VALUES
(1)
, (2)
, (3)
, (4)
, (5)
, (6)
, (7)
) Days (dy)
CROSS APPLY
(
SELECT dt = DATEFROMPARTS(YEAR(@Date), 2, dy)
) dates
WHERE DATENAME(WEEKDAY, dates.dt) = 'Saturday';
As I noted above, inline is better . But if worried about performance, I wouldn't use DATENAME and a string comparison. Also, I think you can safely use TOP (1) in the main SELECT to avoid processing any rows past Saturday, i.e. something like "SELECT TOP (1) FirstSat = dates.dt ... ORDER BY dy".
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 10, 2017 at 4:30 pm
ScottPletcher - Monday, April 10, 2017 2:53 PMAs I noted above, inline is better . But if worried about performance, I wouldn't use DATENAME and a string comparison. Also, I think you can safely use TOP (1) in the main SELECT to avoid processing any rows past Saturday, i.e. something like "SELECT TOP (1) FirstSat = dates.dt ... ORDER BY dy".
I had not realised that this had turned into a 'my function is better than yours' competition.
If you reread my post, you will not find me suggesting that I have come up with a highly optimised greased-weasel of a function, nor was that my intent.
Instead, I came up with an alternative way of skinning the same cat and amplified your suggestion of using an iTVF. That was my intent.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply