Amol Patil

select REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')  from table

Create FUNCTION [dbo].[FinencialYearStartDate] (@Date DATETIME) RETURNS VARCHAR(10) AS BEGIN DECLARE @RetVal varchar(10) Declare @Month int Declare @Year int Select @Month=Month(@Date) Select @Year=Year(@Date) -- Assuming that the financial year is starting from april  if @Month>3  Begin    --Set @RetVal=cast(@Year As varchar(10))+'-'+cast(right('0' + rtrim(@Year+1),2) as varchar(2))    Set @RetVal='04/01/'+cast(@Year As varchar(10))   End  Else   Begin     --Set @RetVal=cast(@Year-1 as varchar(10))+'-'+cast(right('0' + rtrim(@Year),2) as varchar(2)); Set @RetVal='04/01/'+cast(@Year-1 As varchar(10))   End  Return @RetVal End ================================================ select dbo. FinencialYearStartDate ('02/02/2013') ==================Our Put====================== 04/01/2012

CREATE FUNCTION [dbo].[FinencialYear] (@Date DATETIME) RETURNS VARCHAR(7) AS BEGIN DECLARE @RetVal varchar(7) Declare @Month int Declare @Year int Select @Month=Month(@Date) Select @Year=Year(@Date) -- Assuming that the financial year is starting from april if @Month>3  Begin Set @RetVal=cast(@Year As varchar(10))+'-'+cast(right('0' + rtrim(@Year+1),2) as varchar(2)) End Else Begin Set @RetVal=cast(@Year-1 as varchar(10))+'-'+cast(right('0' + rtrim(@Year),2) as varchar(2)); End Return @RetVal End ================================================ select dbo.FinencialYear('02/02/2013') ==================Our Put====================== 2012-13

 both generates the GUID of datatype of uniqueidentifier NEWID() generates the GUID in random order whereas NEWSEQUENTIALID() generates the GUID in sequential order Example:- ----Create Test Table for with default columns values CREATE TABLE temptable ( Column1 uniqueidentifier DEFAULT NEWID (), Column2 uniqueidentifier DEFAULT NewSequentialID ()) ----Inserting five default values in table INSERT INTO  temptable  DEFAULT VALUES INSERT INTO  temptable  DEFAULT VALUES INSERT INTO  temptable  DEFAULT VALUES INSERT INTO  temptable  DEFAULT VALUES INSERT INTO  temptable  DEFAULT VALUES SELECT *  FROM  temptable

----how many days before you need declare @day int set @day=15 SELECT name FROM sys.objects WHERE type = 'P' AND DATEDIFF(D,modify_date, GETDATE()) < @day SELECT name FROM sys.objects WHERE type = 'U'  AND DATEDIFF(D,modify_date, GETDATE()) < @day SELECT name,type FROM sys.objects WHERE type = 'U' AND  DATEDIFF(D,modify_date, GETDATE()) < @day

DECLARE @mydate DATETIME SELECT @mydate = GETDATE () SELECT CONVERT ( VARCHAR ( 25 ), DATEADD ( dd ,-( DAY ( @mydate )), @mydate ), 101 ) ,  'Last Day of Previous Month' UNION SELECT CONVERT ( VARCHAR ( 25 ), DATEADD ( dd ,-( DAY ( @mydate )- 1 ), @mydate ), 101 ) AS Date_Value ,  'First Day of Current Month' AS Date_Type UNION SELECT CONVERT ( VARCHAR ( 25 ), @mydate , 101 ) AS Date_Value , 'Today' AS Date_Type UNION SELECT CONVERT ( VARCHAR ( 25 ), DATEADD ( dd ,-( DAY ( DATEADD ( mm , 1 , @mydate ))), DATEADD ( mm , 1 , @mydate )), 101 ) ,  'Last Day of Current Month' UNION SELECT CONVERT ( VARCHAR ( 25 ), DATEADD ( dd ,- DAY ( DATEADD ( mm , 1 , @mydate ))- 1 ), DATEADD ( mm , 1 , @mydate )), 101 ) , 'First Day of Next Month'

----Last Day of Previous Month SELECT DATEADD ( s ,- 1 , DATEADD ( mm , DATEDIFF ( m , 0 , GETDATE ()), 0 )) as  LastDay_PreviousMonth ----Last Day of Current Month SELECT DATEADD ( s ,- 1 , DATEADD ( mm , DATEDIFF ( m , 0 , GETDATE ())+ 1 , 0 )) as  LastDay_CurrentMonth ----Last Day of Next Month SELECT DATEADD ( s ,- 1 , DATEADD ( mm , DATEDIFF ( m , 0 , GETDATE ())+ 2 , 0 )) as  LastDay_NextMonth ResultSet: LastDay_PreviousMonth ———————– 2012-03-31 10:59:59.000 LastDay_CurrentMonth ———————– 2012-04-30 10:59:59.000 LastDay_NextMonth ———————– 2007-05-31 23:59:59.000 

DECLARE @my int DECLARE @myDeduct int DECLARE @day INT DECLARE @mydate DATETIME SET @mydate = getdate() SET @myDeduct = 0 SET DateFirst 1 -- Set it monday=1 (value) --Saturday and Sunday on the first and last day of a month will Deduct 1 IF (DATEPART(weekday,(DATEADD(dd,-(DAY(@mydate)-1),@mydate))) > 5) SET @myDeduct = @myDeduct + 1 IF (DATEPART(weekday,(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)))) > 5) SET @myDeduct = @myDeduct + 1 SET @my = day(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate))) select (((@my/7) * 5 + (@my%7)) - @myDeduct) as Working_Day_for_month Output:- 23 days for ( August 2011)

Step 1:- Create SQL Function /****** Object: UserDefinedFunction [dbo].[getTotalDaysInMonth] ******/ SET ANSI_ NULL S ON GO SET QUOTED_IDENTIFIER ON GO   -- ============================================= -- Author: -- Create date: &lt;25th July, 2009&gt; -- Description: -- ============================================= CREATE FUNCTION [ dbo ] . [ getTotalDaysInMonth ] ( -- Add the parameters for the function here @anydateofMonth DATETIME ) RETURNS INT AS BEGIN -- Declare the return variable here DECLARE @totalDaysInMonth INT -- Add the T-SQL statements to compute the return value here   DECLARE @givendate DATETIME SET @givendate = @anydateofMonth   SET @givendate = STR ( YEAR ( @givendate ) ) + '-' + STR ( MONTH ( @givendate ) + 1 ) + '-01'   SELECT @totalDaysInMonth = DATEPART ( dd, DATEADD ( DAY , - 1 , @givendate ) )   -- Return the result of the f...

CREATE PROCEDURE dbo.SearchClients ( @ClientFirstName varchar ( 20 ) = null , @ClientLastName varchar ( 20 ) = null , @ClientHasTattoo bit = null , @JobFinished bit = null , @JobTypeID smallint = null , @ArtistID Smallint = null , @JobTotalHoursMinimum float = null , @JobTotalHoursMaximum float = null , @AdvertisingID smallint = null , @ClientDOBMinimum datetime = null , @ClientDOBMaximum datetime = null , @ClientStreetAddress varchar ( 75 ) = null , @ClientCity varchar ( 20 ) = null , @ClientState varchar ( 2 ) = null , @ClientZip varchar ( 10 ) = null , @ClientHomePhone varchar ( 14 ) = null , @ClientWorkPhone varchar ( 14 ) = null , @ClientEmail varchar ( 50 ) = null , @ClientSex bit = null , @JobDateMinimum datetime = null , @JobDateMaximum datetime = null , @JobTitle varchar ( 50 ) = null ) AS IF @JobFinished IS NULL AND @JobTypeID IS NULL AND @ArtistID IS NULL AND @JobTotalHoursMaximum IS NULL A...

DECLARE @mydate DATETIME SELECT @mydate = GETDATE() SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) , 'Last Day of Previous Month' UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value, 'First Day of Current Month' AS Date_Type UNION SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) , 'Last Day of Current Month' UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) , 'First Day of Next Month'

DECLARE @EndOfPrevWeek DateTime DECLARE @StartOfPrevWeek DateTime --get number of a current day (1-Monday, 2-Tuesday... 7-Sunday) SET @TodayDayOfWeek = datepart(dw, GetDate()) print @TodayDayOfWeek --get the last day of the previous week (last Sunday) SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate()) print @EndOfPrevWeek --get the first day of the previous week (the Monday before last) SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate()) print @StartOfPrevWeek --get the last day of the previous week (last Sunday) print DATEADD(dd, 7-@TodayDayOfWeek, GetDate()) --get the first day of the previous week (the Monday before last) print DATEADD(dd, -(@TodayDayOfWeek-1), GetDate())

• Use views and stored procedures instead of heavy-duty queries. This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see. • Try to use constraints instead of triggers, whenever possible. Constraints are much more efficient than triggers and can boost performance. So, you should use constraints instead of triggers, whenever possible. • Use table variables instead of temporary tables. Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available in SQL Server 2000 only. • Try to use UNION ALL statement instead of UNION, whenever possible. The UNION ALL statement is much faster than UNION, because UNION ALL stat...

Finding Missing Auto-Increment Id from table: 1)Step First: We first create table as follows: create table missingGap(id int identity(1,1),val varchar(50)) 2)Step Two: Now we have to insert few records ,it’s quite state forward insert into missingGap(val)values(1) insert into missingGap(val)values(2) insert into missingGap(val)values(3) insert into missingGap(val)values(4) insert into missingGap(val)values(5) insert into missingGap(val)values(6) insert into missingGap(val)values(7) 3)Step Three : Lets see what is in data store: select * from missingGap id Val 1 1 2 2 3 3 4 4 5 5 6 6 7 7 4) Step Four : Delibrately Create A gap in auto increment id by deleting few records delete from missinggap where id in (2,3,4) id Val 1 1 5 5 6 6 7 7 5) Step Five: Lets finding the missing Entry: select a.id+1 MissingFrom,min(b.id)-1 MissingTill from missingGap a,missingGap b where a.id group by a.id having a.id+1 < min(b.id) The Result: + ------------...

using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; namespace MSACCESS { class Program { static void Main(string[] args) { string strDestPath = "I:\\Trial\\school.mdb"; SqlConnection sqlcon=new SqlConnection("Data Source=(Local);Integrated Security=SSPI;Initial Catalog=sangram") ; sqlcon.Open(); SqlCommand sqlcmd = new SqlCommand("select * from course", sqlcon); SqlDataReader sqldr = sqlcmd.ExecuteReader(); LoadFromSql(sqldr,strDestPath,"course"); Console.ReadKey(); } private static void LoadFromSql(SqlDataReader dr1, string Path,string table) { OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source = " + Path ); olecon.Open(); OleDbCommand olecmd = new OleDbCommand(); olecmd.Connection = olecon; string strCno,strCname,strDuration,strFees; while (dr1.Read()) { string ...

CREATE PROCEDURE Dbo.Sp_BackUpRestore (   @Process VARCHAR (10), -- specify the process name (example - restore or backup) @DatabaseName VARCHAR (50), -- specify database name   @ Path VARCHAR ( MAX ) -- specify full path   )   AS   BEGIN DECLARE @DQuery varchar ( MAX )   DECLARE @ErrorMessage NVARCHAR( MAX )   IF @Process<> '' AND @DatabaseName<> '' AND @ Path <> ''   BEGIN   IF @Process= LOWER ( 'backup' )   BEGIN BEGIN TRY   IF EXISTS ( SELECT [Name] FROM sys.databases WHERE [Name]=@DatabaseName )   BEGIN   SET @DQuery= 'BACKUP DATABASE ' +@DatabaseName+ ' TO DISK =N' '' +@ Path + '\' +@DatabaseName+ '.bak' '' PRINT (@DQuery)   EXECUTE (@DQuery)   END   ELSE   BEGIN   PRINT ( 'Database Dose not Exists in...

First create a view like this: CREATE VIEW GetPassword AS SELECT LOWER ( LEFT (NEWID(),8)) as GenPassword Execute this above view in database Then create a function like this: CREATE FUNCTION Dbo.GeneratePassword() RETURNS NVARCHAR(10) AS BEGIN DECLARE @Password NVARCHAR(10) SET @Password=( SELECT GenPassword from GetPassword) RETURN (@Password) END Execute this above funtion in database Calling Function: SELECT dbo.GeneratePassword() as Password

Step 1 First we create a user defined function GetFirstDate function,it is look like this CREATE FUNCTION dbo.GetFirstDate(@OriginalDate datetime) RETURNS varchar (50) AS BEGIN DECLARE @FirstDate datetime SET @FirstDate=DATEADD( MONTH ,-1,DATEADD( DAY ,-DATEPART( Day ,@OriginalDate)+1, @OriginalDate)) RETURN Convert ( varchar (50),@FirstDate,101) END In this function we get first date of the previous month from given actual date value. Step 2 Create a user defined function GetLastDate function,it is look like this CREATE FUNCTION dbo.GetLastDate(@OriginalDate datetime) RETURNS varchar (50) AS BEGIN DECLARE @LastDate datetime SET @LastDate=DATEADD( DAY ,-DATEPART( DAY ,@OriginalDate), @OriginalDate) RETURN Convert ( varchar (50),@LastDate,101) END In this function we get last date of the previous month from given actual dat...

Step 1 Create a Encryption user defined function,it is look like this CREATE FUNCTION Dbo.Encryption(@ Value as NVARCHAR(100)) RETURNS NVARCHAR(100) AS BEGIN DECLARE @EncryptedValue NVARCHAR(100) DECLARE @ Index INT DECLARE @Increment INT SET @EncryptedValue = '' SET @ Index = 1 SET @Increment = 128 WHILE @ Index <= LEN(@ Value ) BEGIN SET @EncryptedValue = @EncryptedValue + NCHAR (ASCII( SUBSTRING (@ Value , @ Index , 1)) + @Increment + @ Index - 1) SET @ Index = @ Index + 1 END RETURN @EncryptedValue END GO Step 2 Create a Decryption user defined function,it is look like this CREATE FUNCTION dbo.Decryption(@EncrypteValue NVARCHAR(100)) RETURNS NVARCHAR(100) AS BEGIN ...

CREATE PROCEDURE dbo.SearchClients ( @ClientFirstName varchar ( 20 ) = null , @ClientLastName varchar ( 20 ) = null , @ClientHasTattoo bit = null , @JobFinished bit = null , @JobTypeID smallint = null ) AS SELECT DISTINCT Clients.ClientID, Clients.ClientFirstName, Clients.ClientLastName, Clients.ClientStreetAddress, Clients.ClientCity, Clients.ClientState, Clients.ClientZip, Clients.ClientHomePhone, Clients.ClientWorkPhone, Clients.ClientEmail, Clients.ClientDOB, Clients.ClientSex, Clients.ClientHasTattoo, Clients.AdvertisingID FROM Job RIGHT OUTER JOIN Clients ON Job.ClientID = Clients.ClientID WHERE (Clients.ClientFirstName LIKE ISNULL(@ClientFirstName, Clients.ClientFirstName) + ' %' ) AND (Clients.ClientLastName LIKE ISNULL(@ClientLastName, Clients.ClientLastName) + ' %' ) AND (Clients.ClientHasTattoo = ISNULL(@Client...