Comments by GregStevens

GregStevens 14-Jul-11 15:20pm View

Deleted

Fantastic! I will update the tip with this version. Im' only going to modify it slightly because you need to substract 2, not 1, if DATEPART(dw, @EndDate) < DatePart(dw, @StartDate)

GregStevens 14-Jul-11 10:08am View

Deleted

Well, as I suspected: it returns 4, even though the correct answer should be 2. (The number of week days BETWEEN 7/15 and 7/15 is zero, so you can't count 7/15 itself as 1).

So the way to get this expression to be correct is to add one more term, just like my Oracle example has above: a term that basically says "If the weekday of the EndDate is before the weekdate of the StartDate, then subtract another 2".

If you can give me the syntax for how to do that in SQL server, we can add it to what you've already provided above, and it should be correct!

GregStevens 13-Jul-11 14:55pm View

Deleted

How fantastically weird. I wonder why?

In the example where StartDate is 7/15/2011 and EndDate is 7/19/2011, the raw difference is 4, and neither the start nor the end is a sunday or saturday, so the last two terms are zero.

As a result, to get a result of 3, DATEDIFF(wk, @StartDate, @EndDate) would have to be.... 1/2 ? That can't be, documentation says that DATEDIFF returns an integer.

Can you verify what DATEDIFF(wk, @StartDate, @EndDate) returns when StartDate is 7/15/2011 and EndDate is 7/19/2011?

GregStevens 13-Jul-11 12:35pm View

Deleted

P.S. if you have access to an SQL server database, please check, though: it's possible that I am wrong about how (DATEDIFF(wk, @StartDate, @EndDate) functions. If (DATEDIFF(wk, @StartDate, @EndDate) returns 1 when StartDate is 7/15/2011 and EndDate is 7/19/2011, then the above expression will work!

GregStevens 13-Jul-11 12:34pm View

Deleted

I don't think that works, because it only accommodates 3 of the 4 points in my list above: it starts with the raw difference, it subtracts the full weeks, and it corrects for days that fall on the weekend; however, it does not correct for weekends that occur before a partial week is up.

But it's close! The problem is, I don't think it returns the correct result for 7/15/2011 to 7/19/2011. The answer should be 2. But I think it returns 4.

All it needs it one more expression that basically says "IF the day of the week of StartDate is later than the day-of-the-week End Date, then subtract another 2 days."

Let's figure this out! :-) How would you do that expression in SQL Server?

GregStevens 13-Jul-11 10:03am View

Deleted

SELECT DATEDIFF(d, date1, date2) as Days FROM TABLE

This does not return Week Days, it returns Calendar days. I don't believe there is a built-in function in SQL server that does weekdays. However, I think that my Oracle expression above could be modified to accomplish the same thing in SQL Server. It could take some tweaking, though. SQL Server has "CASE" instead of "DECODE" to check values, and as you observed there is no "TO_CHAR" function.

If you can come up with a complete expression that translates my Oracle SQL into SQL server, let me know!

GregStevens 13-Jul-11 9:20am View

Deleted

Digimanus is correct: if you notice in the tip above, it says "Oracle SQL", and I included the note below it: "I know that date formatting and certain functions like DECODE are different for MySQL and SQL Server. Can someone provide the equivalent of this expression using MySQL or SQL Server syntax?"

If you are able to substitute the correct SQL Server functions (instead of DECODE and TO_CHAR, which are Oracle functions), and get this to work in SQL Server, please let me know and I will include it in this tip! Or post it as an alternate.

Thanks.

GregStevens 12-Jul-11 22:46pm View

Deleted

A good observation, and an important distinction, you are correct. The more accurate title would be "to get weekdays" -- since that is invariant not specific to the quirks of a company or a culture. I will change it.

Thank you for the feedback!

GregStevens 5-Jul-11 23:35pm View

Deleted

Thanks for your help!

GregStevens 5-Jul-11 18:51pm View

Deleted

Newbie question: if I have a tip that is generically for SQL, in the future should I try to give examples in only ANSI SQL syntax? Or when needed, should I try to give examples in all major flavors (SQL Server, Oracle, MySQL)? What's the best approach on here? Thanks for your help.

GregStevens 5-Jul-11 18:48pm View

Deleted

Thank you very much for adding the MySQL tag to this tip!

And yes, it's fairly common for MySQL, especially in PHP code, to surround named entities (table names, field names, database names). Often these can be dropped, but there are certain cases where they are needed (e.g. certain entities in MySQL may contain hyphens, but the grave accent must be included to make sure it is interpreted as a hyphenated name instead of some kind of subtraction LOL).

GregStevens 5-Jul-11 16:26pm View

Deleted

You are absolutely correct -- when initially submitting this tip, I thought I had used the traditional U+0060 (Grave accent) character surrounding MySQL fields, but apparently somehow either I typed it wrong or it was incorrectly converted. I have gone back and fixed it now, thank you!

GregStevens 5-Jul-11 16:24pm View

Deleted

You are absolutely correct -- when initially submitting this tip, I thought I had used the traditional U+0060 (Grave accent) character surrounding MySQL fields, but apparently somehow either I typed it wrong or it was incorrectly converted. I have gone back and fixed it now, thank you!

Comments by GregStevens (Top 13 by date)