advanced sql function to select the most recent saturday

Hello

i need a sql statement or a function that can get the latest saturday of the month.

EXMPALE
the past saturday was the 17th
1. i want to run a report that goes to that saturday and readall after that saturday
LVL 1
Chris JonesLead Application Web DeveloperAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
OK, since I am confined to my sick bed just now, thought I would play with a generic "week commencing" function regardless of @@datefirst settings...

the idea is you create the function (a once off job in a query window on the server), you can then use it interactively to retrieve any week commencing - such as a saturday in your case...

You could use it directly in the query itself, or call it seperately to get the week commencing result and then plug that result into your query (would perform better).

The function is below, but to call it seperately to get the week commencing you would use :

select dbo.udf_get_weekcommencing ('20100426',6)   --  where '20100426' is a recognized date for your server and 6 is the day number (as explained in the function)

to use it with your query, you would use :

SELECT *
FROM JobHistorySummary jhs
LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID
INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID
WHERE  jhs.MediaSetName= 'Weekly'
AND jhs.EndTim >= dbo.udf_get_weekcommencing ('20100426',6)

Anyway, have a look below at the function, might be interesting... You still have to format the date correctly (ie the "find" variable)

which might mean something like :

Dim ymd As String = find.ToString("yyyyMMdd")

then string in ymd instead of find e.g.

"select dbo.udf_get_weekcommencing ('" & ymd & "',6)"

however, if you want the previous saturday as at "now" then you dont have to worry about stringing in any data - just get sql server to use it's own function for "now". e.g.

"select dbo.udf_get_weekcommencing (getdate(),6)"


Anyway, there is probably nothing more I can do for you, so, hope it all works out well, and still here to answer questions if needed....


create function udf_get_weekcommencing (@thedate datetime, @thedayofweek int)
returns datetime
as
begin

-- The week days are regarded as Mon=1, Tue=2, Wed=3, Thu=4, Fri=5, Sat=6, Sun=7 
-- and is used as the second parameter - so if you want the week commencing to be
-- Sun, then use 7 as the second parameter, or
-- Sat, then use 6 as the second parameter, or
-- Mon, then use 1 as the second parameter - I guess you get the picture by now.
-- This routine will return the week commencing for any date, regardless of @@datefirst

   declare @RawDay int
   declare @RawDate datetime

   set @RawDate = dateadd(d,datediff(d,0,@thedate),0) -- set to start of day
   set @RawDay = (((DATEPART(dw, @rawdate) - 1) + (@@DATEFIRST - 1)) % 7) + 1

   if @RawDay < @thedayofweek
      set @rawdate = dateadd(d,-7+@thedayofweek-@rawday,@rawdate)
   else
      set @rawdate = dateadd(d,@thedayofweek-@rawday,@rawdate)

   return @rawdate

end   

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
using datepart(weekday, getdate()) and @@datefirst you shall find how many days to subtract from getdate() to fall to the previous saturday
http://msdn.microsoft.com/en-us/library/ms174420.aspx

note: what should happen if you run the code on a saturday?
0
 
Patrick MatthewsCommented:
>>i need a sql statement or a function that can get the latest saturday of the month.

What if the current date is such that a Saturday within the current month has not occurred yet?

For example, if this were run on 4 Jun 2010, there will not have been a Saturday in June yet.

What if the current date is itself a Saturday?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Chris JonesLead Application Web DeveloperAuthor Commented:
good question matthewspatrick: do you know of a way i can work around something like that.
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
if the day is saturday i will disable th feature of runing the report.
0
 
HainKurtSr. System AnalystCommented:
try this:

anydate - datepart(weekday, anydate)

this will give you last saturday before any given date...
0
 
HainKurtSr. System AnalystCommented:
and if you have datetime and you want datepart  only use

CAST(FLOOR(CAST( some_datetime AS FLOAT ) ) AS DATETIME)

so if we combine these two

CAST(FLOOR(CAST(
anydate - datepart(weekday, anydate)
AS FLOAT ) ) AS DATETIME) as Previous_Saturday_datepart_only
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
i have a sql statement that i created but i get a syntax error befor AND
        sqlstmt = "SELECT * FROM JobHistorySummary jhs LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID WHERE  '" & find & "' - datepart(weekday, '" & find & "') AND jhs.MediaSetName= '" & jobtype & "'"

Open in new window

0
 
HainKurtSr. System AnalystCommented:
'" & find & "' -  ...
-->
" & find & " -

find is number right? if yes, no need to wrap it with '...
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
sorry i was not clear with my varables

corect find is the current date and it gives th error in the sql at
datepart(weekday,'" & find & "')
between the weekday and the ,
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
NEW STATEMENT
        sqlstmt = "SELECT * FROM JobHistorySummary jhs LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID WHERE  jhs.MediaSetName= '" & jobtype & "' AND '" & find & "' - datepart(weekday,'" & find & "') '"

Open in new window

0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
ok i see i need to pass it a single number EX date
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
i get the error
Unclosed quotation mark before the character string ''.

STATEMENT

"SELECT * FROM JobHistorySummary jhs LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID WHERE  jhs.MediaSetName= 'Weekly' AND '1' - datepart(weekday,'1') '"

Open in new window

0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
hello i tried the exmpale above and i get a syntax error near AS
        'sqlstmt = "SELECT * FROM JobHistorySummary jhs LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID WHERE  jhs.MediaSetName= '" & jobtype & "' AND CAST(FLOOR(CAST('" & find & "'- datepart(weekday, '" & find & "')AS FLOAT ) ) AS DATETIME) as Previous_Saturday_datepart_only'"

Open in new window

0
 
HainKurtSr. System AnalystCommented:
check here

http://www.sqlusa.com/bestpractices/datetimeconversion/

for example if your passed date is in this format dd/mm/yyyy, you should use

convert('23/10/2016', datetime, 103) -- dd/mm/yyyy

so you should use

sqlstmt = "SELECT * FROM JobHistorySummary jhs LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID WHERE  jhs.MediaSetName= '" & jobtype & "' AND some_date <= convert('" & find & "', datetime, 103) - datepart(weekday, convert('" & find & "', datetime, 103))'"

0
 
HainKurtSr. System AnalystCommented:
oops, one fix, extra ' at the end part...

sqlstmt = "SELECT * FROM JobHistorySummary jhs LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID WHERE  jhs.MediaSetName= '" & jobtype & "' AND some_date <= convert('" & find & "', datetime, 103) - datepart(weekday, convert('" & find & "', datetime, 103))"

and if you get any error, please post the value of sqlstmt here...
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
Hello
i get a incorrect syntax error near my varable find
find = '04-26-2010'
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
my sql statement
        sqlstmt = "SELECT * FROM JobHistorySummary jhs LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID WHERE  jhs.MediaSetName= '" & jobtype & "' AND some_date <= convert('" & find & "', datetime, 110) - datepart(weekday, convert('" & find & "', datetime, 110))"

Open in new window

0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
ALSO here is the sql statement with the values from the varables
"SELECT * FROM JobHistorySummary jhs LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID WHERE  jhs.MediaSetName= 'Weekly' AND '04-26-2010' <= convert('04-26-2010', datetime, 110) - datepart(weekday, convert('04-26-2010', datetime, 110))"

Open in new window

0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
new revised statement with displayed varale values
"SELECT * FROM JobHistorySummary jhs LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID WHERE  jhs.MediaSetName= 'Weekly' AND jhs.EndTim <= convert('04-26-2010', datetime, 110) - datepart(weekday, convert('04-26-2010', datetime, 110))'"

Open in new window

0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
any ideals?
0
 
Mark WillsTopic AdvisorCommented:
OK, couple of issues....


What value do you get from:

select @@datefirst

If it is 7 then,

select convert(datetime, '04-26-2010', 101) - datepart(weekday, convert(datetime, '04-26-2010', 101))

will give you the previous saturday, if it is not seven then you do have to do a bit of extra maths.

Next, your SQL statement is not formatted correctly (need to fix those convert statements and there seems to be an extra signle quote at the end):

"SELECT * FROM JobHistorySummary jhs LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID WHERE  jhs.MediaSetName= 'Weekly' AND jhs.EndTim <= convert(datetime, '04-26-2010', 101) - datepart(weekday, convert(datetime, '04-26-2010', 101))"

Then using "find"  replace 04-26-2010  with " & find & "  (leave all single quotes etc as is)

If you can convert "find" to a yyyymmdd string then you should not have to worry about the convert (but convert is ver explicitly telling SQL how to read the date string and is a good thing to do).

0
 
Mark WillsTopic AdvisorCommented:
Oh, that yyyymmdd business ? as an example, try:

select convert(datetime,'20100426') - datepart(weekday, '20100426')
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
i tried trhe script above and it slected everything.
0
 
Mark WillsTopic AdvisorCommented:
Is that good or bad ?
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
bad i only want to get the last saturday and up

EXMAPLE

friday,saturday,sun,monday,tuesday,wensday,thursday,friday

I ONLY NEED
saturday,sun,monday,tuesday,wensday,thursday,friday
0
 
Mark WillsTopic AdvisorCommented:
OK, then what do you get from:

select @@datefirst
0
 
Mark WillsTopic AdvisorCommented:
Also, are we sure we want "<="  in that  "where" clause for jhs.EndTim ?  Looks like it should be >=
0
 
Mark WillsTopic AdvisorCommented:
Typing up some dummy data, and setting datefirst to be 7 (ie select @@datefirst  returns 7)

Then the following returns data from the 24th onwards :

SELECT *
FROM JobHistorySummary jhs
LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID
INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID
WHERE  jhs.MediaSetName= 'Weekly'
AND jhs.EndTim >= convert(datetime,'20100426') - datepart(weekday, '20100426')

0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
Awesome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.