Sqlserver

Update Multiple Tables with one Statement

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

DateFirst in SQL Server

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.


Calculate businessdays between two dates

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

(continue reading…)


Migrating from MS SQL Server to MYSQL

Migrating from MS SQL to MYSQL is a big pain. there are no good tools to use for migration of the all the datbase objects, right from tables , data to stored procedures. We all know that Microsoft SQL server has a very good User Interface but when we come back to MYSQL we donot enjoy the same previlages as we do in SQL server.

Coming to the tools available to download for a good GUI, we have very less options, MYSQL query , PHPadmin are some of them. There are some tools which can be used to migrate the tables and data from the MSSQL to MYSQL. The MYSQL migration tool is also available to migrate the tables. But iam not sure that we can also migrate the stored procedures, but when i tried to migrate a small application i totally failed to shift the stored procedures to MYSQL. I have to migrate them manually. I have to check the syntax manually for each and every line and then execute the procedure.
There are some points which we have to keep in mind while migrating the database from MSSQL to MYSQL. The MYSQL statements has to end with semi-colon , even the ending of the for loop has to closed by a semi-colon.

When we migrate the tables from MSSQL to MYSQL we do not get the perfect script which defines the primary keys and the default values. We have to turn them on manually.

So please be carefull while doing the application and choosing the database, the migration of the database is same as a small project. :)

wish you happy programmin


Get Number of Days in a Month Function

This article shows the ways to get the number of days in a month in both sqlserver and mysql.

Getting the number of days in a month is quite easy because it is going to be either 30 or 31 days, with the exception of February, which can either have 28 or 29 days depending if it is a leap year or not.

  • January,March,May,July,August,October,December — 31 Days
  • April,June,September,November – 30 Days
  • February – 28 Days (Non Leap Year), 29 (Leap Year)

Leap year should be divisible by 4, should not be divisible by 100 or should be divisible by 400.

(continue reading…)


  •   
  • Copyright © 1996-2010 BlogmyQuery - BMQ. All rights reserved.
    iDream theme by Templates Next | Powered by WordPress