How to find first name,Middle name,last name in sql

SELECT
LEFT(transaction_claimant_Firstname,CHARINDEX('|',transaction_claimant_Firstname + '|')-1) AS FirstName,
CASE WHEN LEN(transaction_claimant_Firstname) - LEN(REPLACE(transaction_claimant_Firstname,'|','')) > 1  THEN PARSENAME(REPLACE(transaction_claimant_Firstname,'|','.'),2) ELSE NULL END AS MiddleName,
CASE WHEN LEN(transaction_claimant_Firstname) - LEN(REPLACE(transaction_claimant_Firstname,'|','')) > 0 THEN PARSENAME(REPLACE(transaction_claimant_Firstname,'|','.'),1) ELSE NULL END AS LastName
FROM tablename

how to add ‘st’,’nd’,’rd’,’th’ to dates in SQL Server

Create function [dbo].[get_Custome_date]
(
    @date datetime = null
)
returns nvarchar(50)
as begin
     
    declare @d int,
    @m nvarchar(15),
    @y nvarchar(4),
    @end nvarchar(1),
    @return nvarchar(50)
 
    if @date is null
        set @date=getdate()
    select @d=datepart(d, @date), @m=datename(m, @date), @Y=
datename(yyyy,@date), @end=right(convert(nvarchar(2), @d),1)
    set @return=
        convert(nvarchar(2), @d)
        +case
            when @d in(11, 12, 13) then 'th'
            when @end='1' then 'st'
            when @end='2' then 'nd'
            when @end='3' then 'rd'
            else 'th'  
        end
        +' '+@m+' '+@y
    return @return
 
end


======

select dbo.get_Custome_date(getdate()) AS Customedate

Result is

3rd November 2013

Various sql Finction

1.How to calculate age

SELECT DATEDIFF(DD,'1981-01-05 00:00:00.000',GETDATE())/365 AS AGE

2.How to calulate first day of previous month
select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)

Here 0 indicate as default date of sql(01/01/1900)

3. How to calculate last day of previous month

SELECT GETDATE()-DATEPART(dd,GETDATE())

Left padding with zero in sql

  SELECT RIGHT(REPLICATE('0',10) + CAST(isnull(cast(56589 as decimal(8,2)),0) AS VARCHAR(10)),10)

SQL 2005 and SQL 2000 implementation of ROW_NUMBER

-- SQL 2005 version

SELECT 
    RowNumber = ROW_NUMBER() OVER (ORDER BY c.LastName ASC)
    ,c.LastName
    ,c.FirstName
FROM SalesLT.Customer c

And the SQL 2000 version:

SELECT 
    RowNumber   = IDENTITY(INT,1,1)
    ,c.LastName
    ,c.FirstName
INTO #Customer_RowID
FROM SalesLT.Customer c
ORDER BY c.LastName ASC

Encrypt Or Decrypt QueryString at Asp.net gridview Hyperlink using .net 3.5

Encrypt Or Decrypt QueryString at Asp.net gridview Hyperlink using .net 3.5

1.Include the namespace
using System.Text;
using System.Globalization;
using System.Security;
using System.Security.Cryptography;
using System.IO;

2.Add Hyperlink in template field of gridveiw

<asp:TemplateField HeaderText="Reference No." SortExpression="fullref">
                <ItemTemplate>      
                <asp:hyperlink
                runat="server"
                id="hyperlink1"
             
                text='<%# Eval("fullref") %>'>
                </asp:hyperlink>
                </ItemTemplate>
            <HeaderStyle BackColor="#660066" ForeColor="White"></HeaderStyle>
                </asp:TemplateField>  

3. Add the menthod to encrypt

 private byte[] key = { };
    private byte[] IV = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xab, 0xcd, 0xef };
 public string Encrypt(string stringToEncrypt, string SEncryptionKey)
    {
        try
        {
            key = System.Text.Encoding.UTF8.GetBytes(SEncryptionKey);
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            byte[] inputByteArray = Encoding.UTF8.GetBytes(stringToEncrypt);
            MemoryStream ms = new MemoryStream();
            CryptoStream cs = new CryptoStream(ms,
              des.CreateEncryptor(key, IV), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            return Convert.ToBase64String(ms.ToArray());
        }
        catch (Exception e)
        {
            return e.Message;
        }
    }


4.Use the follwing menthod to bind encrypted querystring on rowdatabaound event of gridveiw


  protected void GDVClientView_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        try
        {
            if (e.Row.RowType != DataControlRowType.DataRow) return;

            if (e.Row.DataItem == null) return;

            HyperLink hlobj = (HyperLink)e.Row.FindControl("hyperlink1") ;

            hlobj.NavigateUrl = String.Format("warrantdetailclient.aspx?fullref={0}",
                                                       Server.UrlEncode(Encrypt(hlobj.Text, "r0b1nr0y")));
        }
        catch (Exception ex)
        {
            //
        }

    }


5.Now on next page use the decript method as:
 protected void Page_Load(object sender, EventArgs e)
    {
 if (Request.Params["fullref"] != null)
        {
            string decrpval = Decrypt(Request.Params["fullref"].ToString(), "r0b1nr0y");
}
}

     private byte[] key = { };
    private byte[] IV = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xab, 0xcd, 0xef };
    public string Decrypt(string stringToDecrypt, string sEncryptionKey)
    {
        byte[] inputByteArray = new byte[stringToDecrypt.Length + 1];
        try
        {
            key = System.Text.Encoding.UTF8.GetBytes(sEncryptionKey);
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            inputByteArray = Convert.FromBase64String(stringToDecrypt);
            MemoryStream ms = new MemoryStream();
            CryptoStream cs = new CryptoStream(ms,
              des.CreateDecryptor(key, IV), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            System.Text.Encoding encoding = System.Text.Encoding.UTF8;
            return encoding.GetString(ms.ToArray());
        }
        catch (Exception e)
        {
            return e.Message;
        }
    }

Cannot resolve the collation conflict In LINQ

Cannot resolve the collation conflict IN LINQ Can be resolved using AsEnumerable() with Table Like as mention below

 DataClassesDataContext tdc = new DataClassesDataContext();

        var  warid = (from id in tdc.SP_Cases.AsEnumerable()

                      join defe in tdc.Defendant_Masters.AsEnumerable() on id.WarrantID   equals defe.WarrantID

                      join defAd in tdc.Defendant_Addresses.AsEnumerable() on defe.CID equals defAd.Def_Id

                     where id.WarrantID == "SPC1080"

                     select new { id.WarrantID, id.JudgCost, id.EntryDate,defe.DefendantName,defAd.Add1 }).FirstOrDefault();

        if (warid != null)

        {

            Label1.Text = warid.WarrantID;

            Label2.Text = warid.JudgCost.ToString();

            Label3.Text = warid.DefendantName;

            Label4.Text = warid.Add1;

        }