In real time applications there might be situation where you need update details of one table using details of another or update two or tables using one statement. Below is Example of how you can do that in SQL Server
Update t1 Set t1.col1 = t2.col1 from Table t1 inner join Table t2 on t1.col = t2.col where Condtn
Below is MYSql Example
Update Table t1 inner join Table t2 on t1.col = t2.col Set t1.col1 = t2.col1 where Condtn
Select @@DateFirst
returns the current value, for a session, of SET DATE FIRST, i.e it returns first day of week, TinyInt Value from 1 through 7 representing Monday to Sunday as shown Below.
1 for Monday 2 for Tuesday 3 for Wednesday 4 for Thursday 5 for Friday 6 for Saturday 7 for Sunday
For Example, If the first day of week is Sunday (US) , Then @@DateFirst would return 7.
If the first day of week is Monday (Europe), Then @@DateFirst Would return 1.
SET DateFirst = 3
The above statement would set Wednesday as first day of the week for the current Session.
‘July 1st 2009 is a wednesday
SET DateFirst = 1 -- or SET LANGUAGE Italian; Select DATEPART(dw, '20090701')
would return 3 (since Wednesday is 3rd day from Monday)
SET DateFirst = 7 -- or SET LANGUAGE us_english; Select DATEPART(dw, '20090701')
would return 4 (since Wednesday is 4th day from Sunday)
DATEPART return the weekday according @@DateFirst.
This Example shows how to calculate the calculate the workdays between two given dates.
CREATE FUNCTION dbo.GetBusinessDays
(
@startDate SMALLDATETIME,
@endDate SMALLDATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @dateDiff INT;
SET @dateDiff = DATEDIFF(DAY, @startDate, @endDate)+1;
RETURN
(
SELECT @dateDiff/ 7 * 5 + CASE WHEN (@dateDiff % 7) = 0 THEN 0
ELSE
CASE
When (DATEPART(WEEKDAY, @endDate ) + @@DateFirst -1)%7 = 0 and (@dateDiff % 7) =1 Then (@dateDiff % 7)-1 –If it is sunday
When (DATEPART(WEEKDAY, @endDate ) + @@DateFirst -1)%7 = 0 and (@dateDiff % 7) <>1 Then (@dateDiff % 7)-2 –If it is sunday
When (DATEPART(WEEKDAY, @endDate ) + @@DateFirst -1)%7 =6 Then (@dateDiff % 7) – 1 –If it is Saturday
ELSE (@dateDiff % 7)
END — Inner Case
END
);
END