Disable And Enable Trigger

Disable   And  Enable Trigger

Disable  the  Trigger

DISABLE TRIGGER TRIGGER_NAME ON WHICH_TABLE;

Enable the Trigger

ENABLE TRIGGER TRIGGER_NAME ON WHICH_TABLE;

--
--

Regards,

Basheer Ahamed

 

Get Week Count Of Year..

GET WEEK COUNT OF YEAR..

 

CREATE FUNCTION ISOweek  (@DATE DATETIME)

RETURNS INT

AS BEGIN  

 DECLARE @ISOweek INT 

  

SET @ISOweek = DATEPART(wk,@DA+1-DATEPART(wk,CAST(DATEPART(yy,@DATE) AS CHAR(4))+'0104')   

--Special cases: Jan 1-3 may belong to the previous year  

    IF (@ISOweek=0)      

    SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy,@DATE) - 1    AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1   

    -- Special case: Dec 29-31 may belong to the next year 

      IF ((DATEPART(mm,@DATE)=12) AND       ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))      

      SET @ISOweek=1    RETURN(@ISOweek) END

      GO

     

     

RESULT

      select dbo.ISOweek(GETDATE())

--
--

Regards,

Basheer Ahamed

 

Day Count (month,year) and leap year

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 (Non-Leap Year) - 28 Days February (Leap Year) - 29 Days The only tricky part here is determining whether it is a leap year or not.  Basically, a year is a leap year if one of the following conditions are met: The year must be divisible by 4 and must NOT be divisible by 100. The year must be divisible by 400.

Based on these conditions, the following user-defined function returns the number of days in a month for a given input date:

1)      GET Month Count...

CREATE FUNCTION [dbo].[ufn_GetDaysInMonth] ( @pDate    DATETIME )

RETURNS INT

AS

BEGIN

 

    RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31

                WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30

                ELSE CASE WHEN (YEAR(@pDate) % 4    = 0 AND

                                YEAR(@pDate) % 100 != 0) OR

                               (YEAR(@pDate) % 400  = 0)

                          THEN 29

                          ELSE 28

                     END

           END

 

END

GO

 

 

 

2) GET First Day in Month…

 

CREATE FUNCTION [dbo].[ufn_GetDaysInMonth] ( @pDate    DATETIME )

RETURNS INT

AS

BEGIN

 

    SET @pDate = CONVERT(VARCHAR(10), @pDate, 101)

    SET @pDate = @pDate - DAY(@pDate) + 1

 

    RETURN DATEDIFF(DD, @pDate, DATEADD(MM, 1, @pDate))

END

GO

 

The process of determining if it is a leap year or not given an input date can be very useful in other functions such as a function that will determine the number of days in a year.  It would be a good idea to make it a separate user-defined function by itself and simply calling it from the [dbo].[ufn_GetDaysInMonth] function.  The following code assumes that a user-defined function called [dbo].[ufn_IsLeapYear] exists which accepts a date as a parameter and returns an integer value of 1 if it is a leap year or a value 0 if it is not.

3)      Get Year Count…

CREATE FUNCTION [dbo].[ufn_IsLeapYear] ( @pDate    DATETIME )

RETURNS BIT

AS

BEGIN

 

    IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR

        YEAR( @pDate ) % 400 = 0

        RETURN 1

 

    RETURN 0

 

END

 

 

 

 

Remove Char(10) or Enter in Excel sheet

Remove   Char(10) or Enter in Excel sheet
Click Alt F11
 then Create Macro Page
then Put this code
 
Option Explicit

Sub RemoveRtns()
Dim cel As Range
For Each cel In ActiveSheet.UsedRange
cel = Replace(cel, Chr(10), " ")
Next
End Sub


then Run  Macro

Stuff

select cast(STUFF('154903',3,2,9)as int)
15903
select cast(STUFF('154903',3,2,'')as int)
1503

bluk insert

Create a table with the following structure

1.create table bulk_insert_test

2.(

3.    employee_id int identity(1,1),

4.    first_name varchar(30), 

5.    last_name varchar(30),

6.    address varchar(100)

7.)

Note that the table has two extra columns employee_id and address

Now this query will fail

1.BULK INSERT bulk_insert_test

2.   FROM 'g:\test.txt'

3.   WITH

4.     (

5.        FIELDTERMINATOR =',',

6.        ROWTERMINATOR = '\n'

7.      )

Becuase the table has four columns and text file has data for only two columns. In this case you can import data to specific columns using the following methods

1 Use a View

Create a view that has only required columns

1.create view vw_bulk_insert_test

2.as

3.select first_name,last_name from bulk_insert_test

 

 

Now use BULK INSERT using this view

1.BULK INSERT vw_bulk_insert_test

2.   FROM 'g:\test.txt'

3.   WITH

4.     (

5.        FIELDTERMINATOR =',',

6.        ROWTERMINATOR = '\n'

7.      )

Name it as format.txt Now use BULK INSERT using this format file

1.BULK INSERT bulk_insert_test

2.   FROM 'g:\test.txt'

3.with (formatfile = 'g:\format.txt')

3 Use OPENROWSET 

1.INSERT INTO bulk_insert_test(first_name,last_name)

2.SELECT 

3.    * 

4.FROM 

5.    OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=NO;FMT=FixedLength;Database=g:\', test#txt)

As you see method 3 doesn't require any extra work if the number of columns are different

DROP CONSTRAINT

ALTER TABLE  [tblexternalload]

DROP CONSTRAINT [DF_tblexternalload_last_modifydate]