advanced sql function to select the most recent saturday

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?

>>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?

good question matthewspatrick: do you know of a way i can work around something like that.

if the day is saturday i will disable th feature of runing the report.

try this:

anydate - datepart(weekday, anydate)

this will give you last saturday before any given date...

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

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 & "'"

Select allOpen in new window

'" & find & "' -  ...
-->
" & find & " -

find is number right? if yes, no need to wrap it with '...

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 ,

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 & "') '"

Select allOpen in new window

ok i see i need to pass it a single number EX date

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') '"

Select allOpen in new window

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'"

Select allOpen in new window

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))'"

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...

Hello
i get a incorrect syntax error near my varable find
find = '04-26-2010'

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))"

Select allOpen in new window

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))"

Select allOpen in new window

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))'"

Select allOpen in new window

any ideals?

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).

Oh, that yyyymmdd business ? as an example, try:

select convert(datetime,'20100426') - datepart(weekday, '20100426')

i tried trhe script above and it slected everything.

Is that good or bad ?

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

OK, then what do you get from:

select @@datefirst

Also, are we sure we want "<="  in that  "where" clause for jhs.EndTim ?  Looks like it should be >=

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')

Awesome